<?php
/*  ================================================================== *\
    (C) Copyright 2010 by Kenneth Downs

    This file is part of Triangulum 

    Triangulum is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 3 of the License, or
    (at your option) any later version.

    Triangulum is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with Triangulum; if not, write to the Free Software
    Foundation, Inc., 51 Franklin St, Fifth Floor,
    Boston, MA  02110-1301  USA
    or visit http://www.gnu.org/licenses/gpl.html
\*  ================================================================== */
/**
 *  This file contains the entire Triangulum Database Builder 
 *  
 *  Quick Start: {@link main The main() method} is where to start.
 *  
 *  @author Kenneth Downs <ken@secdat.com>
 *  @package Triangulum
 *
 */

# THIS IS WONDERFUL, KEEP THIS.  AS OF 12/30/10 ALL DEBUG
# TESTS RUN WITH NO NOTICES OR WARNINGS WHATSOEVER, WE 
# SHOULD ALWAYS MAINTAIN THAT WONDERFUL STATE OF AFFAIRS
error_reporting(E_ALL);


# KFD 12/31/10 Rem'd out because it does not behave
#              the same way it used to.  Should do
#              --yaml=./tests/004.002.nocolon and work
#              as if I had done: triBuild.php ./tests/004.002.nocolon
#
#  TAG: ARGUMENTS   Search for that to see other changes
/*
$arguments = array();
if ( $argc > 1 ) {
    foreach( $argv as $arg_pos=>$argument ) {
        if ( $arg_pos > 0 ) {
            if ( stripos( $argument, '=' ) !== false ) {
                $argVal = explode('=', $argument );
                $argVal['0'] = substr( $argVal['0'], 2 );
                $arguments[$argVal['0']] = $argVal['1'];
            } else {
                $arg = substr( $argument, 2 );
                $arguments[$arg] = true;
            }
        }
    }
}
print_r($arguments);


if ( isset( $arguments['yaml'] ) ) {
    $dir = dirname($arguments['yaml']) .'/';
    $file = basename($arguments['yaml']);
} else {
    $dir = getcwd().'/';
    $file = 'sample.dd.yaml';
}
*/
# KFD 12/31/10 REM (END)

if(count($argv)==1) {
    # KFD 12/18/10 Really not sure about directories
    #$dir = realpath(dirname(__FILE__)).'/';
    $dir = getcwd()."/";
    
    $file= "sample.dd.yaml";
}else {
    $dir = realpath($argv[1]).'/';
    $file = basename($argv[1]).".dd.yaml";
}


$progdir = realpath(dirname(__FILE__)).'/';

# Do this only once
include_once($progdir.'spyc.php');

# Instantiate and run the builder
include 'triBuildPostgres.php';
# KFD 12/31/10 ARGUMENTS
#$builder = new triBuildPostgres($arguments);
$builder = new triBuildPostgres();
$builder->main($dir,$file,$progdir); 
 

/**
 *  Read these docs if you want to hack the builder itself.
 *  
 *  Quick Start: {@link main The main() method} is where to start.
 *  
 *  Other Notes on comments:
 *  - "SOMEDAY" marks areas where I think we should put something
 *              in but do not want to get sidetracked.
 *  - "NOTUSED?" means I think maybe it is not used, once all tests
 *              pass, if it is definitely not being used it can
 *              be removed.
 *
 *  @package Triangulum
 *  @author Kenneth Downs <ken@secdat.com>
 *  @version 0.0.1
 *  
 *
 */
class triBuild {
    # =========================================================
    # Properties overwritten by platform-specific classes
    # =========================================================
    /**
     * Target platform. 
     *
     * This protected property is blank in the base class
     * {@link triBuild}, and is overridden in each particular
     * platform-specific child path
     * 
     * @var String Target platform
     */ 
    protected $platform = '';

    /**
     * Platform-specific extra text to append to CREATE TABLE
     *
     * Some platforms need a string appended to the end of
     * create table commands, such as "ENGINE=InnoDB" for
     * MySQL or "ALTER TABLE SET OWNER POSTGRES" for Postgres.
     * 
     * This protected property defaults to a semi-colon and
     * newline, and is overridden if necessary in platform-specific
     * child classes
     * 
     * @var String Platform-specific CREATE TABLE suffix
     */ 
    protected $platformTableExtra = ";\n";
    

    # =========================================================
    # Properties overwritten by platform-specific (END)
    # =========================================================
    /**
     * Complete path to project directory.
     *
     * Must be passed in as the first parameter to {@link main}.
     * 
     * @var String Path to project directory
     */ 
    private $dir = '';
    /**
     * Complete path to program directory.
     * 
     * Must be passed in as the third parameter to {@link main}.
     * 
     * @var String Path to project directory
     */ 
    private $progdir = '';
    

    /**
     *  A list of errors.
     *  
     *  A nested list of errors that are generated during processing.
     *  If errors are encountered, each error is slotted into the
     *  particular stage that was processing when the error occurred.
     *  
     *  The array might look like:
     *  <pre>
     *  Array (
     *      'SPEC_PROCESS'=>array(
     *          'This is an error'
     *         ,'This is another error'
     *      )
     *      ,'SPEC_VALIDATE'=>array(
     *          'There is no column "tiddlywinks"'
     *         ,'Table X has no columns defined'
     *      )
     *  )
     *  </pre>
     */
    private $errors = array();
    /**
     * var int Running count of errors 
     */
    private $errorCount = 0;
    /**
     * A list of warnings.
     * 
     * This array has the same structure as {@link errors}.
     * 
     * @var array
     */
    private $warnings = array();
    /**
     *  var int Running count of warnings.
     */
    private $warningCount = 0;

    /**
     *  var int Stores value of time() when processing began. 
     */
    private $time = 0;
    /**
     *  var int Stores value of time() when a stage began.
     */
    private $timeStage = 0;
    /**
     *  var string TAG of the currently executing stage.
     */
    private $stage = '';
    
    /**
     * Lists problems that prevent building for a platform.
     * 
     * Triangulum aims to support all features on all platforms,
     * but sometimes this is just not possible.  MySQL has so 
     * many limitations that some features cannot be supported at
     * all, and quirks in other platforms prevent complete expression
     * of some features.
     * 
     * As the spec is built, this array is populated with the 
     * reasons why a platform cannot support the current spec.
     * Any platform with no entries in this array can be 
     * supported.
     * 
     * As of this writing, it is not yet determined what to do
     * with this.  We will print it out in the log and write
     * it out in the debug output, and later on use it to 
     * throw errors if trying to build for a platform that
     * cannot be supported.
     * 
     * @var Array lists problems for a particular platform
     */
    private $platformProblems = array(
        'db2'=>array()
       ,'mssql'=>array()
       ,'mysql'=>array()
       ,'oracle'=>array()
       ,'postgres'=>array()
    );
    
    /* ========================================================== *\
     * 
     * Operational properties
     * 
    \* ========================================================== */
    
    /**
     * File-by-file results from importing YAML files to arrays.
     * 
     * Key is filename.  Each element is the exact array returned
     * by Spyc.php when the YAML was converted to arrays.
     * 
     * Built by: {@link loadFiles}
     * 
     * @var Array File-by-file results from SPYC.
     */
    private $specs = array();
    
    /**
     * File-by-file line numbers from SPYC.
     * 
     * Top key is filename.  Nested key
     * is the compound key tree of an item or property.  A file named
     * 'simple.dd.ymal' that looks like this:
     * 
     * <pre>
     * main:
     *     customers:
     *          
     *          company_name:
     *              type: char(30)
     *              desc: Company Name
     *  
     * </pre>
     *  
     * Will have this result in $lines
     *  
     * - [simple.dd.ymal]=>
     *    - [main] => 1
     *    - [main.customers] => 2
     *    - [main.customers.company_name] => 4
     *    - [main.customers.company_name.type] => 5
     *    - [main.customers.company_name.desc] => 6
     * 
     * Built by: {@link loadFiles}
     * 
     * @var array file-by-file line numbers from SPYC.
     */
    private $lines = array();
    
    /**
     * Nested list of files loaded.
     * 
     * A nested list of files that have been loaded.  Each entry
     * is an array of sub-entries.  The sub-entries come from the
     * "includes" option inside of each file.
     * 
     * Built by: {@link loadFiles} and used by {@link mergeFiles} to
     * determine the sequence that files should be processed.
     * 
     * @var array
     */
    private $files = array();
    
    /**
     * Complete detailed merge history.
     * 
     * The keys of this array are entity names.  Entity names,
     * also known as "full names" are formed as a dot-delimited
     * list of the object's parents.  Column "A" inside of table "B"
     * in module "C" would be named "C.B.A".
     * 
     * Each element is a sub-array.  The keys of the sub-array are
     * the source files the history came from.  
     * 
     * @var Array
     */
    private $mergeHistory = array();
    
    /**
     * Merged Spec.
     * 
     * This spec is produced by {@link filesMerge}.  This array is
     * processed by {@link assignClasses} to determine what class
     * each element is, and then it is copied over in reformatted
     * form to {@link @spec2} by {@link reformatSpec}.
     * 
     * @var Array 
     */
    private $spec = array();
    
    /**
     * The final working form of the specification.
     * 
     * This array is first produced by {@link reformatSpec}, and is
     * then cleaned up a little more (with things like foreign key
     * assignments) and then it is 
     * used by all subsequent routines.  
     * 
     * Each element follows the same form. The top-level elements are
     * "module" and "group".
     * 
     * <pre>
     * $element = array(
     *     'group'=>array(
     *          'staff'=>array(
     *              'properties'=>array(
     *                  'desc'=>'General Staff'
     *                 ,'permsel'=>'Y'
     *                 ,'permins'=>'N'
     *                 ,'permupd'=>'N'
     *                 ,'permdel'=>'N'
     *               )
     *         )
     *         ,'admin'=>array(
     *              'properties'=>array(
     *                  'desc'=>'Admin Users'
     *                 ,'permsel'=>'Y'
     *                 ,'permins'=>'Y'
     *                 ,'permupd'=>'Y'
     *                 ,'permdel'=>'Y'
     *               )
     *          )
     *     ,'module'=>array(
     *         'sales'=>array(
     *             'properties'=>array(
     *                 'desc'=>'Main Module'
     *             )
     *             ,'group'=>array(
     *                  'staff'=>array(
     *                      'properties'=>array(
     *                           'desc'=>'Admin Users'
     *                          ,'permsel'=>'Y'
     *                          ,'permins'=>'Y'
     *                          ,'permupd'=>'Y'
     *                          ,'permdel'=>'Y'
     *                       )
     *                   )
     *             )
     *             ,'table'=>array(
     *                  # tables are a list of nested sub-elements
     *             )
     *          )
     *      )
     *                 
     * </pre>
     * 
     * @var Array
     */
    private $spec2 = array();

    /**
     * The spec of the target database 
     * 
     * @var Array
     */
    private $specCurrent = array();
    
    
    
    
    /**
     * An array of additional properties for items based on platform.
     * 
     * COMMENT: NOTUSED?
     * 
     * NOTE BY KFD 6/5/09.  This was put in specifically for
     *    AUTO_INCREMENTS, but we don't use it for that anymore.
     *    When code is all finished, if this is not being used
     *    it should come out.
     * 
     * This array has "platform" as its key.  Each subarray is keyed
     * on object fullname values, and each of those subarrays is
     * keyed to the particular needs of some feature.
     * 
     * One example is SEQUENCE columns on MySQL.  A sequence column
     * might result in an entry like what is shown below,
     * which means that when the
     * column is created there is some extra stuff to put into the
     * definition.
     * 
     * <pre>
     * $specExtras['mysql'] = array(
     *     'customers.customer'=>array(
     *          'define'=>'NOT NULL AUTO_INCREMENT UNIQUE' 
     *     )
     * );
     * </pre>
     * 
     * 
     * @var Array
     */
    private $specExtras = array();
    
    
    /**
     * Pointers to tables inside of {@link spec2}, key is table name.
     * 
     * @var Array pointers to tables inside of {@link spec2}
     */
    protected $tables2 = array();
    
    
    /**
     * 
     * @var Array sequenced list of tables. See {@link sequenceKeys}
     */
    private $keySequence = array();
    
    
    /**
     * Sequenced columns within each table.
     * 
     * The top level key of this array is the table.
     * Within each table the calculated columns are listed 
     * in order that they should be generated.
     * 
     * @var Array sequenced columns within each table.
     */
    private $colSequence = array();
    
    /**
     * PLACEHOLDER.  A hardcoded list of platforms to build for.
     * 
     * This array will be assigned a list of platforms to build
     * for on a particular run.  Typically this will be only
     * one, but for examples and tests all platforms are built
     * for.
     * 
     * Right now this is a placeholder and is hard-coded with
     * the full list of back-ends.  Later on this will be
     * a run-time parameter.
     * 
     * @var Array list of supported servers                            
     */
    private $platforms = array('mssql','mysql','postgres');

    /**
     * Hardcoded list of error strings.  Loaded from errinfo.yaml
     * 
     * This array is coded by hand directly in errinfo.yaml.
     * Information about the structure of the array can be
     * found in the header comments in errinfo.yaml.
     * 
     * This data is also output along with the dictionary so that
     * programmers can hook error messages and provide their
     * own translations.
     * 
     * @var Array List of error information loaded from errinfo.yaml.
     */
    private $errInfo = array();
    
    /**
     * A complete set of commands for the build script
     * 
     * The end result of a build is a script that will build
     * or upgrade a database.  This array contains an ordered
     * list of commands for the specific platform.  When
     * executed in order, they will create a databse from scratch
     * or update a database.
     * 
     * These scripts are output as "build.mssql.sql", 
     * "build.postgres.sql" and so forth.
     * 
     * @see triggers
     * @see writeScript
     * 
     * @var Array Contains completed build scripts
     */
    private $builds = array(
        'init'    =>array()
       ,'sprocs'  =>array()
       ,'tables'  =>array()
       ,'indexes' =>array()
       ,'triggers_before'=>array()
       ,'triggers'=>array()
       ,'functions'=>array()
       ,'functions_after'=>array()
       ,'close'   =>array()
    );
    
    /**
     * Supplemental List of DECLAREs required for some triggers
     * 
     * Some trigger snippets require DECLARE statements to be
     * made.  They are stored in this array.
     * 
     * @see codeTranslate
     * 
     * @var Array List of extra DECLAREs required for some triggers
     */
    private $declares = array();
    
    /**
     * A master list of all trigger actions in order for each table
     * 
     * This array is written by {@link generateTriggers} and its 
     * helper routine {@link triggerFragment}.  It is then processed
     * by {@link assembleTriggers} to put complete commands into
     * {@link builds}.
     *
     * @see builds
     * @see writeScript
     * 
     * @var Array all unbuilt trigger fragments
     */
    private $triggers = array();
    
    
    /**
     * Holds platform-specific type mapping
     *
     * This array is loaded from the file types.def.yaml by the
     * routine {@link loadTypes}.  It maps Triangulum data types
     * to the types required on specific platforms.
     *
     * @var Array platform-specific type mapping
     */
    private $types = false;
    
    
    /**
     * A hard-coded list of numeric types
     * 
     * @var Array list of numeric types 
     */
    private $numerics = array(
        'tinyint','smalliit','mediumint','int','biginit'
        ,'float','double','money','decimal','numeric'
    
    );
    
    /**
     * A hard-coded list of string types
     * 
     * @var Array list of string types 
     */
    private $strings = array(
        'char','varchar','clob','text'
        ,'tinytext','mediumtext','longtext'
    );
    
    /**
     * List of sections for before/after, assigned in {@link triggerFragment}.
     * 
     * @var Array
     */
    private $tbefore = array(
        'delcons','updcons'
        ,'defaults','calcs','pk','pkcasc'
        ,'fk','fkcasc','fkrest'
        ,'tabcons','colcons'
        ,'upsave'
    );
    private $tafter = array(
        'aggpush','history'
        ,'distpush'
        ,'dominant','queuepos'
    );
    
    /**
     * Obect Constructor
     *
     */
    # KFD 12/31/10 ARGUMENTS
    #function __construct($arguments) {
    #    $this->arguments = $arguments;
    #}
    
   
    # ========================================================================
    #
    # Area: MAIN
    #
    # ========================================================================
    /**
     * A database build begins with a call to main().
     * 
     * This is the master method that begins, executes, and closes a
     * database upgrade/build operation.  This method marks the time
     * when processing began, executes each step, and at the end
     * outputs any errors or warnings.
     *
     * If you want to hack the builder, there are few basic things
     * you need to know before you start:
     * 
     *  - The build process itself is linear.  It is a long series of
     *    steps, each step does one small part of the build.
     *  - Each step is called in sequence from the main() function.
     *    Look at the function itself to see the convention on how
     *    this is done.
     *  - Each step has a "tag", which is used to identify warnings
     *    and errors.  Look at the function source to see how this 
     *    is done.
     *  - Associative Arrays are used very heavily.  Successful hacking
     *    means reading the docs on the array structures and 
     *    <i>carefully</i> working with the arrays.
     *  - This class is and always will be completely standalone, 
     *    with a single exception.  <i>Do not violate this principle,
     *    do not add an outside dependency</i>.
     *  - The only exception to our standalone policy is the "spyc.php"
     *    program, used to parse YAML files.
     *  - Use the {@link log}() function only to say what the program 
     *    is doing, do not use it for warnings or errors.
     *  - Use the {@link error}() function for <i>fatal</i> conditions
     *    that will cause the build to fail.
     *  - Use the {@link warning}() function for non-fatal condititions
     *    that may inconvenience or confuse the programmer or the end-users.
     *  
     * The top-level routines are called in this order:
     * - {@link loadFiles} Loads files to {@link specs}
     * - {@link mergeFiles} Combines files to {@link spec}
     * - {@link mergeReport} Writes a report of the merge
     * - {@link assignClasses} Assigns classes to items in {@link spec}
     * - {@link loadTypes} Loads types.dd.yaml to {@link types}
     * - {@link reformatSpec} Reformats {@link spec} to {@link spec2}
     * - {@link sequenceKeys} Sequences tables by fk dependencies
     * - {@link reformatKeys} Reformats keys, puts stuff where useful
     * - {@link reformatIndexes} Reformats indexes, puts stuff where useful
     * - {@link sequenceColumns} Sequence columns by calculation dependencies. 
     * - {@link cascadeSecurity} PROPOSED - cascade security from modules
     *        down to tables and row/column.
     * - {@link validateStructure} Validates properties and nesting
     * - {@link validateSpecHardcode} Hardcoded validations
     * - {@link getCurrent} PLACEHOLDER to get currrent db structure
     * - {@link loadErrInfo} Loads file errinfo.yaml to {@link errInfo} 
     * - {@link loadSprocs} Loads unified API of sprocs and functions
     * - {@link generateTriggers} Generates snippets into {@link triggers}
     * - {@link assembleTriggers} Assembles snippets in {@link triggers}
     *       into complete triggers in {@link builds}.
     * - {@link writeScript} Writes out complete scripts
     * - {@link writeDictionary} Writes definitions in formats suitable
     *       for various clients.
     * - {@link writeDebug} Writes out most of the arrays used during
     *       processing for debugging purposes.
     * - {@link logWrapUp} Final log entries.
     *
     * @param string Project Directory
     * @param string Top-Level specification file
     * @param string Program Directory (where spyc can be found)
     * 
     * @return boolean Will return true if no errors
     */
    public function main($dir,$file,$progdir) {
        $this->progdir = $progdir;
        $this->dir = $dir;
        
        # Set the time that processing began
        $this->time = time();

        $this->log("========================================================");
        $this->log("");
        $this->log("Triangulum Build Log");
        $this->log("");
        $this->log("========================================================");
        
        /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - *\
         *
         * Steps related to loading and processing spec
         * 
        \* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
        
        # ALL BASIC TESTS COMPLETE 4/23/09
        $this->logStage("LOAD_FILES","Loading Specification Files");
        $this->loadFiles($file,$this->files);

        # ALL BASIC TESTS COMPLETE 4/23/09
        if($this->ok()) {
            $this->logStage("MERGE_FILES","Merging Specification Files");
            $this->mergeFiles($this->files);
            $this->mergeReport();
        }

        # Basic Form OK 4/17/09
        if($this->ok()) {
            $this->logStage("CLASSES","Assign classes to all elements");
            $this->assignClasses('',$this->spec);
        }
        
        # Basic Form OK 4/17/09
        # Probably ok, but waiting of course for lots of tests
        #   Maybe can use the classes tests for this as well
        # 
        if($this->ok()) {
            $this->logStage("TYPES","Load Types from types.def.yaml");
            $this->loadTypes();
        }
        if($this->ok()) {
            $this->logStage("REFORMAT","Reformat Specification");
            $this->reformatSpec('',$this->spec,$this->spec2);
        }

        # Basic form ok 5/4/09, some tests
        if($this->ok()) {
            $this->logStage('TAB_SEQUENCE','Sequence Tables');
            $this->sequenceKeys();
        }
        
        # Basic form ok 5/4/09, some tests
        if($this->ok()) {
            $this->logStage('KEYS','Reformat primary and foreign keys');
            $this->reformatKeys();
        }
        
        # KFD 12/30/10, broke this out of KEYS and into its own
        if($this->ok()) {
            $this->logStage('AGFT','Mark parent/child tables of fetch/agg');
            $this->reformatKeysTravel();
        }

        # Basic form ok 5/4/09, some tests
        if($this->ok()) {
            $this->logStage('INDEXES','Reformat indexes');
            $this->reformatIndexes();
        }

        # Basic form added 6/13/09 KFD
        if($this->ok()) {
            $this->logStage('COL_SEQUENCE','Sequence Columns');
            $this->sequenceColumns();
        }
        
        
        /*
        if($this->ok()) {
            $this->logStage("SECURITY","Cascade and resolve security");
            #$this->cascadeSecurity();
        }
        */
        
        # Basic Form OK 4/17/09
        # Waiting on tests
        # Need a lot more data, just have bare placeholders now.
        #
        # Ideas:
        #   pick up and report errors from reformat
        #   pick up and report errors from table sequencing
        #   pick up and report errors from column sequencing 
        if($this->ok()) {
            $this->logStage("STRUCTURE","Validate Structure");
            $this->validateStructure();
        }
        if($this->ok()) {
            $this->logStage("VALIDATE","Validate Specification");
            $this->validateSpecHardcode();
        }
        
        /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - *\
         *
         * Steps related to getting picture of current database
         *
         * WIREFRAME ONLY, ABSOLUTELY NO TESTS IN THIS FILE
         * OR THE CLASS getCurrentPostgres
         * 
        \* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
        if($this->ok()) {
            $this->logStage("GET_CURRENT","Get current structure of database");
            $this->getCurrent(); 
        }
        
        /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - *\
         *
         * Generate and write out plan
         * 
        \* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
        # Basic form ok 6/4/09 KFD
        if($this->ok()) {
            $this->logStage('LOAD_ERRINFO',"Load errinfo.yaml to memory.");
            $this->loadErrInfo();
        }
        # Basic form ok 6/4/09 KFD
        # KFD 12/30/10 rem'd out for now.  nothing wrong with it but its
        #     a bit of a distraction
        #if($this->ok()) {
        #    $this->logStage('LOAD_API',"Load Unified API");
        #    $this->loadSprocs();
        #}
        
        # This goes through and implements features that can
        # be implemented column-by-column, w/o regard to sequencing
        # of dependencies.  Also handles those that just might be
        # easier to do that way.  This code is all about what can
        # be put into the table definition.
        if($this->ok()) {
            $this->logStage("COL_CODE","Column-by-column code generation");
            $this->codeByColumns();
        }

        # Second part of code generation is the creation and 
        # assembly of triggers.  All specs that cannot be handled
        # declaratively go into triggers.
        if($this->ok()) {
            $this->logStage("TRIGGERS","Generate all trigger code");
            $this->generateTriggers();
        }
        # Improved as of 5/15/09, but still subject to heavy change
        if($this->ok()) {
            $this->logStage("ASSEMBLE_TRIGS","Assemble Triggers");
            $this->assembleTriggers(); 
        }
        
        # This goes at the end because trigger handling code may
        # write additional properties.  Still not sure if I like that.
        # Will likely want to refactor later
        if($this->ok()) {
            $this->logStage("GEN_TABLE","Generate table add/alter commands");
            $this->writeScript(); 
        }
        
        /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - *\
         *
         * Wrapping it up
         * 
        \* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
        
        # Basic form ok, but only outputting spec2
        if($this->ok()) {
            $this->logStage("WRITE_OUTPUT","Write Output Scripts and Data");
            $this->writeDictionary();
        }
        
        # Final steps, do these whether the build failed or succeeded
        # Basic Form ok 4/17/09
        $this->logStage("DEBUG",'Writing out debug files');
        $this->writeDebug();
        
        $this->logStageClose();
        $this->logWrapup();
        return $this->errorCount==0 ? true : false;
    }

    /**
     * Recursively loads files.
     * 
     * This routine is called for the first time with the top level
     * specification.  It looks for a top-level element called
     * "includes", and if it finds it, it recursively calls itself
     * for each file in the list.  In this way, all files are loaded.
     * 
     * During loading, the array {@link specs} is built.  The array
     * keys are the file names, which include the .dd.yaml extensions,
     * and the values are the results of SPYC processing of the YAML
     * files.
     * 
     * Also during loading, a nested list of all files loaded is
     * built as {@link files}.
     * 
     * Previous Step: none
     * 
     * Next Step: {@link mergeFiles}
     *
     * @param $file name of file to open
     * @param &$filesList reference to position in {@link $files}
     * @return boolean Always returns true
     */
    private function loadFiles($file,&$filesList,$indent='') {
        $dir = $this->dir;

        # First slot the file in the nested list
        $filesList[$file] = array();
        
        $inc = '    ';
        
        # For top-level only, put out a log notice:
        if($indent=='') {
            $this->log("Directory: ".$dir);
            $this->log("Top level file: ".$file);
            $this->log("");
            $this->log("FILE SPECIFICATION LIST:");
            if(!file_exists($dir.$file)) {
                $this->error(
                    "Specification File $file not found."
                    ,'FFNF  File Not Found.'
                );
                return;
            }
        }
        $this->log($indent.$file);
     
        # Load the spec, see if we need to recurse
        $spyc = new Spyc();
        
       
        $this->specs[$file] = $spyc->load($dir.$file);
        $this->lines[$file] = $spyc->lines;
        foreach($spyc->errors as $error) {
            $this->error($file." ".$error
                ,'FDUP Duplicate element or property.'
            ); 
        }
        $x = &$this->specs[$file];
        if(isset($x['includes'])) {
            foreach($x['includes'] as $index=>$iName) {
                # This takes care of the __yaml_line entry
                if(!is_numeric($index) || is_array($iName)) {
                    $this->error("Entry '$index' at line "
                        .$this->lines[$file]['includes.'.$index]
                        ." of $file"
                        .', individual include entries must be'
                        .' indented, start with a dash, list the'
                        .' file, and not have a trailing colon.'
                        ,'FINC Malformed Include Entry.'
                    );
                    continue;
                }
                
                # Work out the file name and try to call it
                $iFile = $iName.'.dd.yaml';
                if(file_exists($dir.$iFile)) {
                    $this->loadFiles(
                        $iFile,$filesList[$file],$indent.$inc
                    );
                }
                else {
                    $this->error(
                        "File $file lists non-existent include: $iFile"
                        ,'FINF Include File Not Found.'
                    );
                    $this->log($indent.$inc.$iFile." (NOT FOUND)");
                }             
            }
            
            # Kill the includes, they would confuse downstream code
            unset($this->specs[$file]['includes']);
        }
        
        # Only at the top level, provide count of files loaded
        if($indent=='') {
            $count = count(array_keys($this->specs));
            $this->log();
            $this->log("Files loaded: ".$count);
        }
        return true;
    }
    

    /**
     * Walks through specs in reverse order and merges them.
     * 
     * Spec files can include other spec files, which can in turn
     * include still more, and so on.  A typical include tree might
     * look like this:
     * 
     * <pre>
     * top_file.dd.yaml
     *     include1.dd.yaml
     *        grandchild_a.dd.yaml
     *        grandchild_b.dd.yaml
     *     include2.dd.yaml
     *        grandchild_c.dd.yaml
     * </pre>
     * 
     * In this case we want to process in this order:
     * <pre>
     *   grandchild_a.dd.yaml
     *   grandchild_b.dd.yaml
     *   include1.dd.yaml
     *   grandchild_c.dd.yaml
     *   include2.dd.yaml
     *   top_file.dd.yaml
     * </pre>
     *
     * The effect of this sequence is that a container file can modify
     * the settings of any file it includes.
     *
     * The algorithm is simple: For any particular file, always
     * process its includes FIRST.  Only when they are processed do you
     * do the file itself.  Peers are processed in the order they are
     * included.
     * 
     * Items may not be duplicated in multiple files.  This is
     * an error.
     * 
     * This program also processes the "extends" property.  If an
     * element explicitly names the "extends" property, the 
     * merge sequence is:
     * - any existing definitions are merged over the class
     * - the new definition is merged over the result
     * 
     * Finally, if an object has no "extends" property, but there is
     * a top-level object with the "type" property, this is an implied
     * use of "extends".  This little shortcut is rather important,
     * it allows a table to contain property-less listings of tables
     * and columns, which can be correctly identified as foreign
     * keys and columns.  More details:
     * 
     * - An item with no properties can be assumed to be a column if
     *   there is a top-level class of the same name with the "type"
     *   property.  This
     *   allows the programmer to leave off the "extends" property
     *   for columns, a big convenience because there will be so many
     *   of those.
     * - All other uses of classes require the explicit use of the 
     *   'extends' property. 
     * 
     * 
     * Previous Step: {@link loadFiles}
     * 
     * Next Step: {@link mergeReport}
     * 
     * Helpers:
     * - {@link deepMerge}
     * - {@link deepLinks}
     * - {@link classMerge}
     * 
     * @see mergeHistory
     * @return boolean Always returns true
     */
    private function mergeFiles($filesList,$level=0) {
        # Give a short explanation
        if($level==0) {
            $this->log("Files are merged in the reverse sequence that they");
            $this->log("are loaded.  Child files are processed before their");
            $this->log("parents.  Peers are processed in the order");
            $this->log("they are listed.");
            $this->log("");
        }

        # Always process the children first, so a higher file
        # is processed after its children, so that it can overwrite
        # them.
        #
        foreach($filesList as $file=>$kids) {
            $this->mergeFiles($kids,$level+1);
            $this->log("Merging File: ".$file);
            $spec = &$this->specs[$file];

            # Recursively merge the arrays.
            foreach($spec as $name=>$details) {
                if(!is_array($details)) {
                    $this->error("Definition '$name:$details' in '$file'"
                        ." at line ".$this->lines[$file][$name]
                        ." is not allowed."
                        ."  Property definitions have no"
                        ." meaning outside of elements."
                        ,'FTOP Property At Top Level.'
                    );
                    continue;
                }

                $this->spec[$name] = $this->deepMerge(
                    $file
                    ,''
                    ,''
                    ,$name
                    ,$details
                );
            }
        }
    }

    /**
     * A recursive helper routine for {@link mergeFiles}.
     *
     * This routine is called by {@link mergeFiles} to merge objects
     * from their individual files into a large unified definition.
     *
     * The program is like array_merge, except that if array values
     * are themselves arrays, those arrays are recursively merged.
     *
     * Along the way, it looks for objects that have an 'extends'
     * property.  It also automatically extends any column that has
     * the same name as a column class.  Column classes are identified
     * because they have the "type" property.
     *
     * This program also builds the debug array {@link mergeHistory}.
     *
     *  KFD 12/18/10.  There is a thorny issue with identifying the
     *                 line and file of an object that extends other
     *                 objects.  Have left it open for now.
     *
     * @param $file
     * @param $oChain
     * @param $cChain
     * @param $name
     * @param $new
     * @return unknown_type
     */
    private function deepMerge($file,$oChain,$cChain,$name,$new) {
        # This actually traps for missing colons in definitions.
        # When the programmer leaves off a colon, the item is created
        # with a numeric key and the text key is lost.
        if(is_numeric($name)) {
            $this->error("Line ".$this->lines[$file][$oChain.$name]
                ." of file $file missing trailing colon."
                ,'FCLN Missing Trailing Colon.'
            );
            return array();
        }

        # if cChain == '--literals--', this is where we are
        # adding explicit object details onto the previously
        # merged class definition.  Must empty out the class
        # definition or code will get confused
        if($cChain=='--literals--.') $cChain = '';

        # An existing mergeHistory entry is an automatic error.
        # Items cannot be defined in two different files, classes
        # are the only way to extend and re-use definitions.
        if(isset($this->mergeHistory[$oChain.$name])) {
            $x = $this->mergeHistory[$oChain.$name];
            $xf= $x['file'];
            $xl= $x['line'];
            $this->error("Duplicate definition, element '$oChain$name',"
                ." originally defined in $xf, line $xl, duplicated in"
                ." $file, line ".$this->lines[$file][$oChain.$name]
                ,'FXDP Duplicate Element In Multiple Files.'
            );
        }

        # Create the merge history item and get pointer
        $this->mergeHistory[$oChain.$name] = array();
        $mh = &$this->mergeHistory[$oChain.$name];
        if($cChain <> '') {
            # Little bit of pointer chasing if class is not empty.
            # We have to find the object we are adding to, and list
            # the line of the "extends" property, that is why this
            # action is happening.
            $xoChain = trim($oChain,'.');
            $xfile = $this->mergeHistory[$xoChain]['file'];
            $mh['file']=$xfile;
            $mh['line']=$this->lines[$xfile][$xoChain.'.extends'];
        }
        else {
            $mh['file'] = $file;
            $mh['line'] =$this->lines[$file][$oChain.$name];
        }
        $line = $mh['line'];

        # Now work out if we are extending a class.  Either it is
        # explicit, or a column is being named.
        $class='';
        if(isset($new['extends'])) {
            $class=$new['extends'];
            unset($new['extends']);
        }
        if($class=='') {
            $nameParts = explode('.',$name);
            $shortName = array_pop($nameParts);
            if(isset($this->spec[$shortName]['type'])) {
                $class = $shortName;
            }
        }

        # Now either continue with class or without
        if($class<>'') {
            $classes = explode(',',$class);

            # Begin with an empty return result
            $retval = array();

            # Now overlay subsequent classes
            $lClass = '';
            while(count($classes)>0) {
                $oneClass =array_shift($classes);
                if(!isset($this->spec[$oneClass])) {
                    $this->error("Element $name of file $file defined at line "
                        .$line." extends class $oneClass."
                        ,'CNTF Class Not Found.'
                    );
                    continue;
                }
                $retval = $this->classMerge($file
                    ,$oChain
                    ,$cChain
                    ,$name
                    ,$this->spec[$oneClass]
                    ,$retval
                    ,$oneClass
                    ,''
                );
                $lClass=$oneClass;
            }

            # At the end, overlay the new spec on top of the results,
            # but only if $cChain is empty.
            if($cChain=='') {
                $retval = $this->classMerge($file
                    ,$oChain
                    ,$cChain
                    ,$name
                    ,$new
                    ,$retval
                    ,'--literals--'
                    ,$lClass
                );
            }
        }
        else {
            # Prepare the return value
            $retval = array();

            # Now recurse child elements.
            foreach($new as $key=>$value) {
                # An empty value for a property is assumed to
                # be an empty array
                if($value=='') $value=array();

                if(!is_array($value)) {
                    $retval[$key] = $value;
                }
                else {
                    $retval[$key] = $this->deepMerge(
                        $file,"$oChain$name.",$cChain,$key,$value
                    );
                }
            }
        }

        # The first time we run across a column in a table we
        # create a class definition for it.
        if(isset($retval['type']) && ! isset($this->spec[$name])) {
            if($oChain<>'') {
                $this->spec[$name]=$retval;
                if(isset($this->spec[$name]['auto']))
                    unset($this->spec[$name]['auto']);
                if(isset($this->spec[$name]['primary_key']))
                    unset($this->spec[$name]['primary_key']);
                $this->mergeHistory[$name] =array(
                    'auto-create'=>"$oChain$name"
                    ,'file'=>$this->mergeHistory[$oChain.$name]['file']
                    ,'line'=>$this->mergeHistory[$oChain.$name]['line']
                );
            }
        }

        return $retval;
    }

    function classMerge($file,$oChain,$cChain,$name,$new,$retval,$class,$lClass) {
        # Get pointer to merge history, add extension stuff
        $mh = &$this->mergeHistory[$oChain.$name];
        $mh['extends'][$cChain.$class] = array();
        $mhe = &$mh['extends'][$cChain.$class];
        if($class!='--literals--') {
            $mhe['file']=$this->mergeHistory[$cChain.$class]['file'];
            $mhe['line']=$this->mergeHistory[$cChain.$class]['line'];
        }
        $mhl = array();
        if($lClass<>'') {
            $mhl = &$this->mergeHistory[$lClass];
        }
        
        # Loop through the new stuff to see what gets added
        # or overwritten
        foreach($new as $key=>$value) {
            # Numeric keys throw out errors at an earlier
            # stage, we do not want to clutter the error log
            # with more errors below, so we skip them here.
            if(is_numeric($key)) continue;
         
            # If does not exist, it is an add
            if(!isset($retval[$key])) {
                # Simple values are logged and added
                if(!is_array($value)) {
                    $mhe['props_added'][$key] = $value;
                    #  = "$key added with value '$value'";
                    $retval[$key]=$value;
                }
                else {
                    $mhe['kids_added'][] = $key; 
                    $retval[$key]=$this->deepMerge(
                        $file,"$oChain$name.","$cChain$class.",$key,$value
                    );
                }
            }
            # But if it does exist, it is an overwrite
            # Overwrites are complete.
            else {
                # Two error traps, array->scalar and vice versa
                $cValue = $retval[$key];
                if(is_array($value) && !is_array($cValue)) {
                    $this->error(
                        "Property '$key' has value '$cValue' in class '$lClass',"
                        ." defined in file {$mhl['file']} at line "
                        .$mhl['line']
                        .", but element '$oChain$name'"
                        ." defined at line "
                        .$this->lines[$file][$oChain.$name] 
                        ." of $file"
                        ." defines '$key' as an element."
                        ,'PEOP Element Overwrites Property.'
                    );
                }
                else if(!is_array($value) && is_array($cValue)) {
                    $this->error(
                        "Element '$key' in class '$lClass',"
                        ." defined in file {$mhl['file']} at line"
                        ." {$mhl['line']}, but element '$oChain$name'"
                        ." defined at line {$mh['line']} of $file "
                        ." defines '$key' as a property with value '$value'."
                        ,'PPOE Property Overwrites Element.'
                    );
                }
                else {
                    # Each is either a scalar or an array, so we are ok
                    if(!is_array($value)) {
                        if($value <> $cValue) {
                            $mhe['props_changed'][$key] = array(
                                'new'=>$value,'old'=>$cValue
                            ); 
                            $retval[$key]=$value;
                        }
                    }
                    else {
                        # If replacing an element, must delete the merge
                        # history, or it will complain of a dupe
                        unset($this->mergeHistory["$oChain$name.$key"]);
                     
                        $mhe['kids_replaced'][] = $key;
                        
                        echo "\nAbout to go deepmerge $file $oChain$name $key $value";
                        $retval[$key]=$this->deepMerge(
                            $file,"$oChain$name.","",$key,$value
                        );
                    }
                }
            }
        }
        return $retval;               
    }

    /**
     * Recurses the merge files and reports to log
     * 
     * Previous Step {@link mergeFiles}
     * 
     * Next Step {@link assignClasses}
     * 
     * @return Boolean Always returns true
     */
    function mergeReport() {
        # Now write out the merge history
        $this->log("");
        $this->log("MERGE HISTORY: ");
        $this->log("");
        foreach($this->mergeHistory as $mhname=>$mhdetails) {
            # Skip any children, they are reported during recursion
            if(count(explode('.',$mhname))>1) continue;
            
            # Call the recursive reporter
            $this->mergeReportRecurse($mhname);
        }
        return true;
    }
    
    function mergeReportRecurse($mhname,$indent='') {
        return;
        # Don't report anything with only one entry, that means
        # there were no changes, it would clutter everything up.
        if(count($this->mergeHistory[$mhname])<2) return;
        
        # Begin by logging the name
        $this->log($indent.'** '.$mhname);
        $indent.='    ';
        foreach($this->mergeHistory[$mhname] as $file=>$info) {
            $this->log($indent."Defined in $file at line {$info['__yaml_line']}");
            foreach($info['propextends'] as $propextend) {
                $this->log($indent.'  '.$propextend);
            } 
            foreach($info['childextends'] as $child) {
                $this->mergeReportRecurse($child,$indent);
            }
        }
    }

    /**
     * Examine elements and determine their classes.
     * 
     * This method examines the properties of all elements and 
     * works out what class each element is: column, group, module, 
     * and so forth.
     * 
     * Some objects can be identified by their properties, such 
     * as columns and groups.  Others, such as tables and modules
     * can only be identified by what they contain.  For this 
     * reason, the program recurses into children first and
     * assigns classes from the deepest items up to the top.
     * 
     * Previous Step: {@link mergeReport}
     * 
     * Next Step: {@link loadTypes}
     * 
     * @return Array List of classes found in a list of elements
     */
    private function assignClasses($oChain,&$spec) {
        # Make a list of classes we found in this list 
        # to pass upward
        $retval = array(); 
     
        foreach($spec as $name=>$details) {
            # This routine is recursive, and properties will be mixed
            # up with lists of child items.  This bit here
            # skips properties.
            if(!is_array($details)) {
                continue;
            }
            
            # Remove __yaml_line
            if(isset($details['__yaml_line'])) {
                unset($spec[$name]['__yaml_line']);
            }
         
            # First problem would be no properties
            if(count($details)==0) {
                # Do a slight trick here to give a better error 
                # message if the name is "upsaves"
                if($name=='upsaves') {
                    $fn = rtrim($oChain,'.');
                    $this->error("Upsave '$fn' has no columns to save,"
                        ." nothing to do!  "
                        .$this->defineHistory($oChain.$name)
                        ,'UPNO Upsave Has Nothing To Do.'
                    );
                }
                else {
                    $this->error("Element $oChain$name has no properties.  "
                        .$this->defineHistory($oChain.$name)
                        ,'PNON Element Has No Properties.'              
                    );
                }
                continue;
            }
            
            # Make a list of child classes, we can use those to
            # guess what this class is.  So recurse the children first
            $kidClasses = $this->assignClasses(
                $oChain.$name.'.',$spec[$name]
            );
            
            # Detecting a group is verbose, do it in advance
            $isColumn = isset($details['type']);
            $isGroup 
                =  isset($details['permsel'])
                || isset($details['permins'])
                || isset($details['permupd'])
                || isset($details['permdel'])
                || isset($details['freejoin'])
                || isset($details['solo']);

            if(isset($details['class'])) {
                $class = $details['class'];
            }
            else if($isGroup) {
                $class = 'group';
            }
            else if(isset($details['type'])) {
                $class = 'column';
            }
            else if(isset($details['parent'])) {
                $class = 'foreign_key';
            }
            else if(isset($details['foreign_key'])) {
                $class = 'upsave';
            }
            else if(isset($details['history'])) {
                $class = 'history';
            }
            else if($name=='upsaves') {
                $class = 'upsave_columns';
            }
            else if($name=='save_new') {
                $class = 'hnew';
            }
            else if($name=='save_old') {
                $class = 'hold';
            }
            else if($name=='deltas') {
                $class = 'hdelta';
            }
            else if(in_array('table',$kidClasses)) {
                $class = 'module';
            }
            else if(in_array('column',$kidClasses)) {
                $class = 'table';
            }
            else if(in_array('foreign_key',$kidClasses)) {
                $class = 'table';
            }
            else {
                $this->error(
                    "Element $oChain$name could not identify class.  "
                    .$this->defineHistory(trim($oChain.$name))
                    ,'CNID Could Not Identify Class.'
                );
                $class='--error--';
            }
            $spec[$name]['class'] = $class;
            $retval[] = $class;
        }
        return $retval;
    }
    
    /**
     * Loads platform data type translations from types.def.yaml
     * 
     * This program is run before {@link reformatSpec} because
     * {@link reformatSpec} needs to be able to expand types.
     * 
     * The file types.def.yaml contains our translations of our
     * own data types to each platform.  It is loaded up into
     * the array "$this->types" and has this form:
     * 
     * [int2] = array(
     *    [db2] => false
     *    [mssql] => smallint
     *    [mysql] => smallint
     *    [oracle] => false
     *    [postgres] => int2
     * [char] = array(
     *    [db2] => char
     *    [mssql] => char
     *    [mysql] => char
     *    [oracle] => char
     *    [postgres] => char
     *  
     *  A value of "false" means that the type is not supported
     *  on that platform.  Use of that type will produce an 
     *  entry in {@link platformProblems}.
     *  
     *  Previous Step: {@link assignClasses}
     *  
     *  Next Step: {@link reformatSpec}
     * 
     * @return Boolean Always Returns True
     */
    function loadTypes() {
        if(!file_exists("types.def.yaml")) {
            $this->error("Could not find file types.def.yaml");
            return;
        }
        
        $spyc = new Spyc();
        $this->types = $spyc->load("types.def.yaml");
        
        # Some cleanup, get rid of the "comment" property
        # for types, we don't need it
        foreach($this->types as $type=>$details) {
            if(isset($this->types[$type]['comment'])) {
                unset($this->types[$type]['comment']);
            }
        }
        
        return true;
    }
    
    /**
     * Reformat the spec from the raw form to {@link spec2}
     * 
     * This very important routine takes the final merged spec
     * from {@link spec} and converts it to our final format
     * in the array {@link spec2}.
     * 
     * This routine also populates the array {@link tables2}
     * with pointers to the specifications for each table.
     * 
     * This routine also expands some properties.  These are
     * properties that we need to parse into smaller pieces
     * for validation or other purposes.  The expansions
     * are these:
     * 
     * - column.type is expanded to:
     *   - xtype  just the type, ie, numeric or char
     *   - xprecision if applicable
     *   - xscale     if applicable
     *   - xtyperror an array of extra parameters if the programmer
     *               typed in something like "char(1,2,3,4)"
     * - column.auto is expanded to:
     *   - xauto the automation 
     *   - xarg  the automation argument
     *   - xtable the argument table where applicable
     *   - xcolumn the argument column where applicable
     *   - xautoerrorcommas an array of extra parameters if the
     *            programmer typed in something like
     *            "sum,orders,order_total" instead of
     *            "sum,orders.order_total".
     *   - xautoerrordots an array of extra values if the programmer
     *            typed in something like "sum.orders.order_total.extra"
     *   
     * Expansions are done by the helper routines listed below.
     * These routines do not directly generate errors, but some
     * error information is stored as properties to be detected
     * and reported by {@link validateStructure}.  The helpers 
     * are given the destination spec2 array by reference, and
     * they write directly to it, they do not return values.
     * 
     * The only expansions not performed here are primary keys and
     * foreign keys, those are worked out after common columns are
     * added by {@link commonColumns}.
     * 
     * The helpers are:
     * - {@link rfLoadTypes}
     * - {@link rfExpandType} 
     * - {@link rfExpandAuto}
     * 
     * Previous Step: {@link loadTypes}
     * 
     * Next Step: {@link sequenceKeys}
     * 
     * @param $src
     * @param $dst
     * @return Boolean Always returns true
     */
    private function reformatSpec($oChain,$src,&$dst) {
        # The main event now is to recurse through the spec
        foreach($src as $name=>$details) {
            # get the class and create the basic element,
            # then get rid of the class, it will get in the way
            $class = $details['class'].'s';
            unset($details['class']); 
            $dst[$class][$name] = array(
                'properties'=>array(
                    'fullname'=>"$oChain$name"
                )            
            );
            
            # If this is a table, make a link to it.
            if($class=='tables') {
                $this->tables2[$name] = &$dst[$class][$name];
            } 
            
            # assemble the properties.  Remove each one as we
            # process it, so we can make the recursive call
            # below w/o worrying about mixing properties and
            # children.
            foreach($details as $key=>$value) {
                if(!is_array($value)) {
                    $dst[$class][$name]['properties'][$key] = $value;
                    unset($details[$key]);
                    
                    # Call out to the expansions, if this class
                    # qualifies for them.
                    if($class=='columns') {
                        if($key=='type') {
                            $this->rfExpandType($oChain,$dst,$name,$value);
                        }
                        elseif($key=='auto') {
                            $this->rfExpandAuto($dst,$name,$value);
                        }
                    }
                }
            }
            
            # Now recurse with the properties removed
            $this->reformatSpec(
                $oChain.$name.'.',$details,$dst[$class][$name]
            );
        }
        return true;
    }

    
    /**
     * All type rewrites
     * 
     * Major work is to expand type/precision/scale out to three
     * values.
     *
     * Also detects identity,increment,serial,sequence and turns
     * them into primary key integers
     *
     * This is a helper to {@link reformatSpec}.  This routine has
     * no return value, it directly writes to the destination
     * spec2 array, which is passed by reference.
     *
     * NOTE nonstandard variable names, $name instead of $column
     *
     * @param $dst Array By reference, the destination array
     * @param $name String Name of current object
     * @param $value String Current property value
     * @return Boolean Always returns true
     */
    private function rfExpandType($oChain,&$dst,$name,$value) {
        # Make a shortcut to where we are writing
        $xdst = &$dst['columns'][$name]['properties'];
        
        # Initialize this extra property, it might change below
        $xdst['xtype'] = $value;

        # These are always done, maybe overwritten below
        $xdst['xprecision'] = '';
        $xdst['xscale'] = '';
        
        # KFD 12/30/10, add trap for identity,increment,serial,sequence
        /*
        $identities = array('identity','increment','serial','sequence');
        if(in_array($identities,$value)) {
            $xdst['xtype'] = 'int';
            $xdst['
        }
        */
        
        # matches will end up as a list of what is inside
        # the parentheses
        $list = array();  # added KFD 12/18/10 to clear notices
        $matches = array();
        preg_match('/\(.*\)/',$value,$matches);
        if(count($matches)>0) {
            $xdst['xtype'] = str_replace($matches[0],'',$value);
            $list = str_replace('(','',$matches[0]);
            $list = str_replace(')','',$list);
            $list = explode(',',$list);
        }
        
        # Now fill in the expanded values
        if(count($list)>0) {
            $xdst['xprecision'] = array_shift($list);
            $xtype = $xdst['xtype'];
            $allowed=array('char','varchar','numeric','decimal');
            if(!in_array($xtype,$allowed) ) {
                $this->error("Column '$oChain$name' has a parameter"
                    ." which is only supported for types "
                    ." 'char', 'varchar', 'decimal'"
                    ." or 'numeric'.  "
                    .$this->defineHistory($oChain.$name)
                    ,'TPP1 Spurious Column Precision.'
                );
            }
        }
        if(count($list)>0) {
            $xdst['xscale'] = array_shift($list);
            # Only allow this for types numeric and decimal
            if($xdst['xtype'] != 'numeric' && $xdst['xtype']!='decimal') {
                $this->error("Column '$oChain$name' has a second parameter"
                    ." which is only supported for types 'decimal'"
                    ." or 'numeric'.  "
                    .$this->defineHistory($oChain.$name)
                    ,'TPP2 Spurious Column Scale.'
                );
            }
        }
        
        # More error checking: types missing first parm
        $parmreq = array('char','varchar','numeric','decimal');
        if(in_array($xdst['xtype'],$parmreq) && !$xdst['xprecision'])  {
            $this->error("Column '$oChain$name' requires at least "
                ." one parameter.  "
                .$this->defineHistory($oChain.$name)
                ,'TPP0 Column Precision Required.'
            );
        }
        
        # Now look up this type in the "types" array and assign
        # the platform types
        $xtype = $xdst['xtype'];
        if(!isset($this->types[$xdst['xtype']])) {
            $this->error("Unrecognized column type '$xtype' for '$oChain$name'"
                .' '.$this->defineHistory($oChain.$name)
                ,'TPNF Unrecognized Column Type.'
            );
        }
        else {
            #foreach($this->types[$xdst['xtype']] as $platform=>$xlate) {
            $xlate = $this->arr($this->types[$xdst['xtype']],$this->platform);
            if(!$xlate) {
                $this->platformProblems[$this->platform][] 
                    ="Column '$oChain$name' is type '$xtype' not"
                    ." supported on this platform. "
                    .$this->defineHistory($oChain.$name);
            }
            else {
                # KFD 12/18/10  Matches often has nothing in it,
                #               currently accounting for this with
                #               no setting
                if(isset($matches[0])) {
                    $xdst['typeplatform'] = $xlate.$matches[0];
                }
                else {
                    $xdst['typeplatform'] = $xlate;
                }
            }
        }
        
        # If we have extras there is an error
        if(count($list)>0) {
            $this->error("Column '$oChain$name' has more than two parameters "
                ."in type definition.  ".$this->defineHistory($oChain.$name)
                ,'TPP3 More Than Two Parameters'
            );
        }
        return true;        
    }

    /**
     * Expands the auto property
     * 
     * This is a helper to {@link reformatSpec}.  This routine has
     * no return value, it directly writes to the destination
     * spec2 array, which is passed by reference.
     * 
     * 
     * @param $dst Array By reference, the destination array
     * @param $name String Name of current object
     * @param $value String Current property value
     * @return unknown_type
     */
    private function rfExpandAuto(&$dst,$name,$value) {
        # Make a shortcut to where we are writing and 
        # initialize our extra parameters
        $xdst = &$dst['columns'][$name]['properties'];
        $xdst['xauto'] = '';
        $xdst['xautoform'] = '';
        $xdst['xautotable'] = '';
        $xdst['xautocolumn'] = '';
        
        # Quick return if empty
        if($value=='') return;

        # Split on comma and assign xauto
        $x = explode(',',$value);
        $xdst['xauto'] = trim(array_shift($x));
        
        # If there is anything left, split that
        if(count($x)>0) {
            $y = array_shift($x);
            $xdst['xautoform'] = $y;
            
            $z = explode('.',$y);
            if(count($z)>0) {
                $xdst['xautotable'] = trim(array_shift($z));
            }
            if(count($z)>0) {
                $xdst['xautocolumn']= trim(array_shift($z));
            }
            
            # If too many dots, store the values as errors
            while(count($z)>0) {
                $xdst['xautoerrordots'][] = trim(array_shift($z)); 
            }
        }
        
        # Another error or list of errors for too many arguments
        while(count($x)>0) {
            $xdst['xautoerrorcommas'][] = trim(array_shift($x)); 
        }
        
        return true;
    }

    
    /**
     * Runs through {@link tables2} and builds {@link keySequence}
     * 
     * This program identifies the order in which primary keys and
     * foreign keys must be built.  The foreign key definition allows
     * a programmer to specify a foreign key in table "CHILD" without
     * actually specifying the columns.  The columns are then
     * automatically added to the table "CHILD" by looking at the
     * primary key of table "PARENT".  
     * 
     * This means that table "PARENT" must have its primary key
     * defined already.  But sometimes a foreign key itself will
     * be part of a child table's primary key, so it does not work to
     * pass all tables and set up their primary keys, then pass all
     * tables and do their foreign keys.  You have to be able to go in
     * order, creating all primary keys for the leaf tables, then 
     * putting foreign keys into their children, then defining the
     * primary keys for those children, then recursing to the next
     * level, and so on.
     * 
     * This routine builds the array {@link keySequence}, which has
     * this structure:
     * 
     * $this->keySequence = Array(
     *      [table]=>fullname
     *     ,[table]=>fullname
     * );
     * 
     * Helper: {@link chaseParents} Finds circular dependencies
     * 
     * Previous Step: {@link reformatSpec}
     * 
     * Next Step: {@link reformatKeys}
     * 
     * @return Boolean Always returns true
     * 
     */
    private function sequenceKeys() {
        # A list of tables that have been sequenced, so we
        # don't list somebody twice
        $tables = array();
        
        # This is tricky.  We are passing through all tables,
        # looking for those whose parents have all been listed.
        # Any table with no unlisted parents gets listed as the
        # next table that can be processed.
        #        
        #
        #        TABLE A            No parents, processed first
        #         |  |  |
        #   Table B  |  Table C     Two children of A, 2nd and 3rd
        #            |  |
        #            Table D        Last table w/all parents
        #                           processed, 4th.
        #
        # Initialize the count so that the first pass will work
        $count = 1;
        while($count > 0) {
            # zero out the count of affected tables.  When we are
            # finished this will stay at zero and the loop will end
            $count = 0;
            
            foreach($this->tables2 as $table=>$tdetails) {
                # Skip a table that has already been sequenced.
                if(in_array($table,$tables)) continue;
                
                # This code works even if there are no parents, it
                # just falls out.  Here we drop out all of the parents
                # that are already sequenced.  If that leaves the table
                # with no unsequenced parents, we sequence it at the
                # current level.
                $fks = $this->arr($tdetails,'foreign_keys',array());
                $parents = array();
                foreach($fks as $fk=>$fkdetails) {
                    $parent = $fkdetails['properties']['parent'];
                    if(isset($this->tables2[$parent])) {
                        $parents[] = $parent;
                    }
                    else {
                        $fullname = $fkdetails['properties']['fullname'];
                        $this->error("Table '$table' has "
                            ."foreign key to undefined table: '$parent'. "
                            .$this->defineHistory($fullname)
                            ,'TNTF Table Not Found.'
                        );
                    }
                }
                foreach($parents as $idx=>$parent) {
                    if(in_array($parent,$tables) || $parent==$table) {
                        unset($parents[$idx]);
                    }
                }
                if(count($parents)==0) {
                    $tables[] = $table;
                    $this->keySequence[] = $table;
                    $count++;
                }
            }
        }
        
        # Step two is to look for tables that have circular fk dependencies.
        # The "caught" array prevents repeat identifications of tables
        # inside of a circle (otherwise a 3-table circle gets reported
        # 3 times)
        $caught = array();
        foreach($this->tables2 as $table=>$tdetails) {
            $this->chaseParents($caught,array(),$table,$table);
        }
    }
    
    private function chaseParents(&$caught,$stack,$tableRoot,$table) {
        # Skip immediately if this table has already been caught
        # in a circular relationship
        if(in_array($table,$caught)) return;
        
        $tdetails = $this->tables2[$table];
        $fks = $this->arr($tdetails,'foreign_keys',array());
        foreach($fks as $fk=>$fkdetails) {
            $parent = $fkdetails['properties']['parent'];

            # If the parent is the original tableRoot, we might
            # have a problem...
            if($parent==$tableRoot) {
                # .... but not if the stack is empty, that means the
                #      table has an fk to itself, which is ok.
                if(count($stack)==0) continue;
                # .... but if stack is not empty that is a problem
                else {
                    $rootFull
                         =$this->tables2[$tableRoot]['properties']['fullname'];
                    $text = '';
                    $caught[] = $tableRoot;
                    foreach($stack as $istack=>$fullname) {
                        $text.=" is child of '$istack' ("
                            .$this->defineHistory($fullname).") ";
                        $caught[] = $istack;
                    }
                    $this->error("Circular Foreign Key Dependency.  Chain "
                        ."is '$tableRoot' (".$this->defineHistory($rootFull).")"
                        .$text." is child of '$tableRoot'"
                        ,'FKCR Circular Foreign Key Dependency.'
                    );
                }
            }
            else {
                # If parent is not the tableRoot, add this table to the
                # stack and keep going
                # KFD 12/18/10.  If fk names non-existent table, remember
                #     that build always keeps going through to end, and
                #     so we have to trap for that bad value here and 
                #     discontinue.
                if(isset($this->tables2[$parent])) {
                    $stack[$parent] 
                        =$this->tables2[$parent]['properties']['fullname'];
                    $this->chaseParents($caught,$stack,$tableRoot,$parent);
                }
            }
        }
        
    }
    
    
    /**
     * Takes the reformatted spec and works out primary and foreign keys.
     * 
     * This program loops through all tables and adds the "primary_key"
     * property, a comma-separted list of columns that make the primary
     * key.
     * 
     * Then it loops through all tables looking for foreign keys, and
     * spells those out in terms of parent, child, and lists of columns.
     * 
     * This program also takes the aggregate automations: sum, count
     * min, max and latest and adds data to the foreign key definitions
     * of the child tables for easier downstream trigger generation.
     * 
     * 
     * Previous Step: {@link sequenceKeys}
     * 
     * Next Step: {@link reformatIndexes}
     * 
     * Helpers:
     * - {@link rkKeys} Process candidate/primary keys
     * - {@link rkFK} Processes one foreign key
     * 
     * @return Boolean Always returns true
     */
    private function reformatKeys() {
        # Now loop through the sequenced tables and fetch foreign
        # key values, then build its primary key.
        foreach($this->keySequence as $table) {
            # get table details
            $tdetails = $this->tables2[$table];

            # Safe retrieval of foreign keys
            $fks = $this->arr($tdetails,'foreign_keys',array());
            
            # KFD 12/18/10.  We have to figure out if this table
            #     has a foreign key to itself.  If it does, we have
            #     to define its pk first.  If it does not, we have
            #     to define fk's first, because the pk might include
            #     them.  We don't have to worry about a mixed case,
            #     fk-to-itself-as-part-of-pk, that is disallowed
            #     because it makes no sense.
            $fktoself = false;
            foreach($fks as $fk=>$fkdetails) {
                if($fkdetails['properties']['parent']==$table) {
                    $fktoself=true;
                    break;
                }
            }
                
            # KFD 12/18/10.  now this is a bummer, based on the flag
            #     we just found, we do them in one order or another,
            #    so I copy/pasted the code.
            if($fktoself) {
                # COPY-PAST WARNING.  THE EXACT CODE IS BELOW IN REVERSE ORDER
                # Step 1: Primary and Candidate Keys
                $this->rkKeys($table);
                
                # Step 2: Foreign Keys
                $tdetails = $this->tables2[$table];
                foreach($fks as $fk=>$fkdetails) {
                    $parent = $fkdetails['properties']['parent'];
                    # The main event occurs in the helper
                    $this->rkFK($table,$parent,$fk,$fkdetails);
                }

            }
            else {
                # COPY-PAST WARNING.  THE EXACT CODE IS ABOVE IN REVERSE ORDER
                # Step 1: Foreign Keys
                foreach($fks as $fk=>$fkdetails) {
                    $parent = $fkdetails['properties']['parent'];
                    # The main event occurs in the helper
                    $this->rkFK($table,$parent,$fk,$fkdetails);
                }
                
                # Step 2: Primary and Candidate keys
                $this->rkKeys($table);
            }
        }
        
        # Finally, go through tables one more time and check for
        # primary keys
        foreach($this->tables2 as $table=>$tdetails) {
            if($this->arr($tdetails['properties'],'primary_key','')=='') {
                $fullname = $tdetails['properties']['fullname'];
                $this->warning("Table '$fullname' has no primary key."
                    .' '.$this->defineHistory($fullname)
                );
            }
        }
        return true;
    }

    /**
     * Helper routine to {@link reformatKeys}, does candidate keys
     *
     * This program builds new list of keys, with a special
     * for "primary_key" for b/w compatibility
     *
     * @param $table Table Name
     * @return Boolean Always returns true
     */
    private function rkKeys($table) {
        # Convert each key assignment into a list of keys
        $keyLists = array();
        foreach($this->tables2[$table]['columns'] as $column=>$cdetails) {
            # This is for the "keys:" property, a comma-separated list
            $keys = $this->arr($cdetails['properties'],'keys','');
            $akeys1 = $keys=='' ? array() : explode(',',$keys);
            foreach($akeys1 as $akey1) {
                $akey1=trim($akey1);
                if($akey1=='primary') $akey1='primary_key';
                $keyLists[$akey1][$column] = $column;
            }

            # This is for the primary_key property, a Y/N
            if($this->arr($cdetails['properties'],'primary_key','')=='Y') {
                $keyLists['primary_key'][$column] = $column;
            }
        }
        foreach($keyLists as $keyList=>$cols) {
            $fn = $this->tables2[$table]['properties']['fullname'];
            $this->tables2[$table]['keys'][$keyList] = array(
                'properties'=>array('fullname'=>"$fn.$keyList")
               ,'keycols'=>$cols
            );
            if($keyList=='primary_key') {
                $this->tables2[$table]['properties']['primary_key']
                        = implode(',',$cols);
            }
        }
        
        # Now a trick to turn the identity variants into a primary
        # key.  But if a primary key already exists, trouble!
        $identities = array('identity','increment','serial','sequence');
        foreach($this->tables2[$table]['columns'] as $column=>$cdetails) {
            # If true, this column becomes the primary key
            if(in_array($cdetails['properties']['type'],$identities)) {
                # unless there is already a primary key
                $fn = $this->tables2[$table]['properties']['fullname'];
                if(!isset($this->tables2[$table]['properties']['primary_key'])) {
                    $this->tables2[$table]['properties']['primary_key'] = $column;
                    $this->tables2[$table]['keys']['primary_key'] = array(
                        'properties'=>array('fullname'=>"$fn.primary_key")
                       ,'keycols'=>array($column=>$column)
                    );
                }
                else {
                    $type = $cdetails['properties']['type'];
                    $this->error("Redundant Primary Key definitions.  Table"
                        ." '$table' defined at ("
                        .$this->defineHistory($fn)
                        .") has column '$column' of type '$type'"
                        ." but also has a primary key defined: "
                        .$this->tables2[$table]['properties']['primary_key']
                        ,'RDPK Redundant Primary Key.'
                    );
                }        
            }
        } 
    }

    /**
     * Helper routine to {@link reformatKeys}, does one foreign key
     *
     * This program does some hardcoding into {@link mergeHistory}, so
     * that columns that came from another table can be tracked.
     *
     * @param $table Table Name
     * @param $parent Parent Table Name
     * @param $fkdetails FK Details
     * @return Boolean Always returns true
     */
    private function rkFK($table,$parent,$fk,$fkdetails) {
        # Get these, we'll need them below
        $prefix = $this->arr($fkdetails['properties'],'prefix','');
        $suffix = $this->arr($fkdetails['properties'],'suffix','');

        # Put link in parent back to child.
        $this->tables2[$parent]['fk_children'][$fk] 
            = array('child'=>$table);
        # Full name of parent table
        $pfull = $this->tables2[$parent]['properties']['fullname'];

        # Shortcut to current table's list of columns
        $cols = &$this->tables2[$table]['columns'];

        # List of properties we take from the primary key column,
        # all else are dropped unceremoniously
        $props = array(
            'type','xtype','xprecision','xscale','typeplatform','keys'
        );

        # This will be a SQL expression that can be used
        # anywhere you need to match tables
        $sqlcols = array();
        $sqlself = array();
        $fkcolspar = array();
        $fkcolschd = array();

        # Grab the parent's primary key and explode it
        $colspar = explode(
            ",",$this->tables2[$parent]['properties']['primary_key']
        );
        foreach($colspar as $colpar) {
            # get shortcut to parent column's properties
            $refcolpar =
                    &$this->tables2[$parent]['columns'][$colpar]['properties'];

            # If the parent column is 'range_from', it does not get copied
            if($this->arr($refcolpar,'range_from','')<>'') continue;

            # Work out some names, and initialize the final
            # properties in the destination table
            $colchild = $prefix.$colpar.$suffix;
            $tfull=$this->tables2[$table]['properties']['fullname'];
            $cfull=$tfull.'.'.$colchild;
            $childprops = array('fullname'=>$cfull);

            # Add an entry to the list of sqlcols
            $sqlself[] = "left.$colchild = right.$colchild";
            $sqlcols[] = "$table.$colchild = $parent.$colpar";
            $fkcolspar[] = $colpar;
            $fkcolschd[] = $colchild;

            if(isset($cols[$colchild])) {
                # No action if defined already, because it gets
                # everything from the class definition
            }
            else {
                # Copy over hard-coded list of approved properties
                foreach($props as $prop) {
                    if(isset($refcolpar[$prop])) {
                        $childprops[$prop] = $refcolpar[$prop];
                    }
                }
                $childprops['primary_key']
                    =$this->arr($fkdetails['properties'],'primary_key','N');
                $this->tables2[$table]['columns'][$colchild]=array(
                    'properties'=>$childprops
                );
                $this->mergeHistory[$tfull]['foreign_keys'][$fk][$colchild]
                    =array(
                        'source'=>$pfull
                        ,'props_added'=>$childprops
                    );
                $this->mergeHistory[$cfull] = array(
                    'source'=>$pfull
                    ,'foreign_key'=>$fk
                    ,'file'=>$this->mergeHistory["$tfull.$fk"]['file']
                    ,'line'=>$this->mergeHistory["$tfull.$fk"]['line']
                );
            }
        }
        
        # Now we will collapse the list of sqlcols into a valid
        # sql expression.
        $this->tables2[$table]['foreign_keys'][$fk]['properties']['sql']
            =implode(" AND ",$sqlcols); 
        $this->tables2[$table]['foreign_keys'][$fk]['properties']['sqlself']
            =implode(" AND ",$sqlself); 
        $this->tables2[$table]['foreign_keys'][$fk]['properties']['colspar']
            =$fkcolspar;
        $this->tables2[$table]['foreign_keys'][$fk]['properties']['colschd']
            =$fkcolschd; 
        $this->tables2[$table]['foreign_keys'][$fk]['properties']['cols']
            =array_combine($fkcolschd,$fkcolspar); 
            
            
            
        return true;
    }
    
        
    /**
     * Marks parent/child tables of fetch/agg 
     * 
     * This program loops through all tables and finds aggregation
     * and fetch functions, and then marks the respective child/parent
     * table if required that the action is necessary
     * 
     * Previous Step: {@link reformatKeys}
     * 
     * Next Step: {@link reformatIndexes}
     * 
     * @return Boolean Always returns true
     */
     private function reformatKeysTravel() {
        #  One more step, find all aggregate automations and
        #  copy certain properties up to parent 
        #
        $autos = array('sum','count','min','max','latest');
        foreach($this->tables2 as $table=>$tdetails) {
            foreach($tdetails['columns'] as $column=>$cdetails) {
                $xaf  = $this->arr($cdetails['properties'],'xauto');
                $xafk = $this->arr($cdetails['properties'],'xautotable');
                $xacol= $this->arr($cdetails['properties'],'xautocolumn');
                
                # KFD 12/30/10, test for fk after
                if(in_array($xaf,$autos)) {
                    $fn = $cdetails['properties']['fullname'];
                    if(!isset($tdetails['fk_children'][$xafk])) {
                        $this->error(
                            "Column '$fn' has automation '$xaf' and names"
                            ." foreign key '$xafk' not known to parent table.  "
                            .$this->defineHistory($fn)
                            ,'FKNF Foreign Key Not Found.'                    
                        );
                        continue;        
                    }
                    $tabchd=$tdetails['fk_children'][$xafk]['child'];
                    if(!isset($this->tables2[$tabchd]['foreign_keys'][$xafk])) {
                        $this->error(
                            "Column '$fn' has automation '$xaf' and names"
                            ." foreign key '$xafk' not known to child table.  "
                            .$this->defineHistory($fn)
                            ,'FKNF Foreign Key Not Found.'                    
                        );
                        continue;        
                    }

                    $this->tables2[$tabchd]['foreign_keys'][$xafk]
                        ['autos'][$xaf][$xacol] = $column;
                }
                if($xaf=='fetch') {
                    $fn = $cdetails['properties']['fullname'];
                    if(!isset($tdetails['foreign_keys'][$xafk])) {
                        $this->error(
                            "Column '$fn' has automation '$xaf' and names"
                            ." foreign key '$xafk' not known to child table.  "
                            .$this->defineHistory($fn)
                            ,'FKNF Foreign Key Not Found.'                    
                        );
                        continue;        
                    }
                    $tabpar=$tdetails['foreign_keys'][$xafk]['properties']['parent'];
                    if(!isset($this->tables2[$tabpar]['fk_children'][$xafk])) {
                        $this->error(
                            "Column '$fn' has automation '$xaf' and names"
                            ." foreign key '$xafk' not known to parent table.  "
                            .$this->defineHistory($fn)
                            ,'FKNF Foreign Key Not Found.'                    
                        );
                        continue;        
                    }
                    $this->tables2[$table]['foreign_keys'][$xafk]
                        ['autos']['fetch'][$xacol] = $column;
                }
            }
        }
        return true;
    }
    

    /**
     * Takes the reformatted spec and reformats indexes
     * 
     * This program loops through all tables converts named indexes
     * from individual columns into group indexes
     * 
     * Previous Step: {@link reformatKeysTravel}
     * 
     * Next Step: {@link sequenceColumns}
     * 
     * @return Boolean Always returns true
     */
    private function reformatIndexes() {
        foreach($this->tables2 as $table=>$tdetails) {
            $keyLists = array();
            foreach($tdetails['columns'] as $column=>$cdetails) {
                # This is for the "keys:" property, a comma-separated list
                $indexes  = $this->arr($cdetails['properties'],'indexes','');
                $aindexes = $indexes=='' ? array() : explode(',',$indexes);
                foreach($aindexes as $index) {
                    $keyLists[trim($index)][$column] = $column;
                }
            }
            foreach($keyLists as $keyList=>$cols) {
                $fn = $this->tables2[$table]['properties']['fullname'];
                $this->tables2[$table]['indexes'][$keyList] = array(
                    'properties'=>array('fullname'=>"$fn.$keyList")
                   ,'idxcols'=>$cols
                );
            }
        
        }
        return true;
    }
    
    /**
     * Sequence columns within tables on calculation dependencies.
     * 
     * This routine examines the columns within each table.  Relevant
     * columns are those that have a FETCH automation or a CALCULATE
     * property.  These are sequenced so that they are executed in
     * the proper order for the calculations to come out correctly.
     * 
     * Previous Step: {@link reformatKeys}
     * 
     * Next Step: {@link validateStructure}
     * 
     * Helpers:
     * - {@link sequenceColsMakeDeps}
     * - {@link sequenceColsOneTable}
     * - {@link chaseColParents}
     * 
     * @return Boolean Always Returns True
     */
    private function sequenceColumns() {
        # Loop through each table
        foreach($this->tables2 as $table=>$tdetails) {
            $this->sequenceColsMakeDeps($table,$tdetails);
            if(isset($this->tables2[$table]['coldeps'])) {
                $this->sequenceColsOneTable($table);
            }
        }
    }
    
    /**
     * Helper to {@link sequenceColumns} generates dependencies for one table.
     * 
     * Also generates all errors for FETCH and calculations.
     * 
     */
    function sequenceColsMakeDeps($table,$tdetails) {
        $columns = $this->arr($tdetails,'columns',array());
        foreach($columns as $column=>$cdetails) {
            # First step here is to look for calculated columns
            $fn   = $this->columnProp($table,$column,'fullname');
            $type = $this->columnProp($table,$column,'type');
            $calculate = $this->arr(
                $cdetails['properties'],'calculate',''
            );
            if($calculate) {
                preg_match_all("/!\w*/",$calculate,$matches);
                $cols = $this->arr($matches,0,array());
                foreach($cols as $col) {
                    $col = substr($col,1);
                    if(!isset($tdetails['columns'][$col])) {
                        $this->error(
                            "Column '$fn' names undefined source column"
                            ." '$col' in its calculate code.  "
                            .$this->defineHistory($fn)
                            ,'CNTF Column Not Found.'
                        );
                        continue;
                    }
                    $this->tables2[$table]['coldeps'][$column][]=$col;
                }
            }
            
            # Next step is to look for fetches
            $xauto = $this->arr($cdetails['properties'],'xauto','');
            if($xauto=='fetch') {
                $xfk = $cdetails['properties']['xautotable'];
                # KFD 12/30/10 These no longer needed because
                #              we made routine reformatKeysTravel()
                /*
                if($xfk=='') {
                    $this->error(
                        "Column '$fn' has a fetch automation but does "
                        ."not name a foreign key.  "
                        .$this->defineHistory($fn)
                        ,'FKRQ Foreign Key Required.'                    
                    ); 
                    continue;       
                }
                if(!isset($tdetails['foreign_keys'][$xfk])) {
                    $this->error(
                        "Column '$fn' has automation 'fetch' and names"
                        ." undefined foreign key '$xfk'.  "
                        .$this->defineHistory($fn)
                        ,'FKNF Foreign Key Not Found.'                    
                    );
                    continue;        
                }
                */
                $xautocol = $cdetails['properties']['xautocolumn'];
                $parent = $tdetails['foreign_keys'][$xfk]
                    ['properties']['parent'];
                if(!isset($this->tables2[$parent]['columns'][$xautocol])) {
                    $this->error(
                        "Column '$fn' has automation 'fetch' and names"
                        ." undefined source column '$xautocol'.  "
                        .$this->defineHistory($fn)
                        ,'CNTF Column Not Found.'                    
                    );
                    continue;
                }
                $ptype = $this->columnProp($parent,$xautocol,'type');
                $pfn   = $this->columnProp($parent,$xautocol,'fullname');
                if($ptype<>$type) {
                    $this->error(
                        "Column '$fn' fetches from '$pfn', but they do"
                        ." no match exactly on type, precision and scale."
                        ." Column '$fn' is '$type' and column '$pfn' is "
                        ."'$ptype'.  "
                        ."Column '$fn' ".$this->defineHistory($fn)
                        ."  Column '$pfn' ".$this->defineHistory($pfn)
                        ,'TPXX Column Type Mismatch'
                    );
                }
                $xfkcols = $tdetails['foreign_keys'][$xfk]
                    ['properties']['colschd'];
                foreach($xfkcols as $col) {
                    $this->tables2[$table]['coldeps'][$column][]=$col;
                } 
            }
        }
    }
    
    private function sequenceColsOneTable($table) {
        $tdetails = $this->tables2[$table];
        
        # A list of columns that have been sequenced, so we
        # don't list somebody twice
        $columns = array();
        
        # Get the column dependencies for this table.  Initialize
        # the columns array with all columns that have no 
        # dependencies themselves.
        $coldeps = $tdetails['coldeps'];
        foreach($coldeps as $column=>$deps) {
            foreach($deps as $idx=>$dep) {
                if(!isset($coldeps[$dep])) {
                    $columns[] = $dep;
                }            
            }
        }
        
        # Initialize the count so that the first pass will work
        # 
        $count = 9999;
        while($count > 0) {
            # zero out the count of affected columns.  When we are
            # finished this will stay at zero and the loop will end
            $count = 0;
            
            foreach($coldeps as $column=>$deps) {
                # Skip a column that has already been sequenced.
                if(in_array($column,$columns)) continue;
                
                # Here we look at the column's dependencies and
                # eliminate any that have already been sequenced.
                # If there are none left at the end, the column
                # is sequenced as the next column to calculate.
                foreach($deps as $idx=>$dep) {
                    if(in_array($dep,$columns)) {
                        unset($deps[$idx]);
                    } 
                }
                if(count($deps)==0) {
                    unset($coldeps[$column]);
                    $columns[] = $column;
                    $count++;
                }
            }
        }
        
        # Here we trap for circular dependencies
        if(count($coldeps)>0) {
            $caught = array();
            
            foreach($coldeps as $column=>$deps) {
                $this->chaseColParents(
                    $table,$coldeps,$caught,array(),$deps,$column,$column
                );
            }
        }
        
        
        # Now go through each column in order and work
        # out if it is a fetch or a calculate
        foreach($columns as $column) {
            $xauto = $this->columnProp($table,$column,'xauto');
            $xcalc = $this->columnProp($table,$column,'calculate');
            if($xauto=='fetch') {
                $xfk =$this->columnProp($table,$column,'xautotable');
                $this->colSequence[$table][$xfk] = 'foreign_key';
            }
            elseif($xcalc<>'') {
                $this->colSequence[$table][$column] = 'calculate';
            }
        }
        return true;
    }
    
    /**
     * 
     */
    private function chaseColparents(
        $table,$coldeps,&$caught,$stack,$deps,$colRoot,$column
        ) {
           
        # Skip immediately if this column has already been caught
        # in a circular relationship
        if(in_array($column,$caught)) return;
        
        # First loop checks if any of the dependencies
        # matches the root, that would be a catch.
        foreach($deps as $dep) {
            if(trim($dep)==trim($colRoot)) {
                $fnRoot = $this->columnProp($table,$colRoot,'fullname');
                $text = "$fnRoot depends on ";
                foreach($stack as $col=>$fullname) {
                    $text.="$fullname, which depends on ";
                }
                $text.="$fnRoot.  ";
                $text.=" $fnRoot ".$this->defineHistory($fnRoot).'  ';
                foreach($stack as $col=>$fullname) {
                    $text.="$fullname  ".$this->defineHistory($fullname).'  ';
                    $caught[] = $col;
                }
                $this->error($text,'X_CCIR Circular Column Dependency.');
                return;
                # <<<<------- EARLY RETURN
                #
                #  A catch sends us back
                #
                # <<<<------- EARLY RETURN
            }
        }
                
        # If nothing caught, recurse
        foreach($deps as $dep) {
            $newDeps = $this->arr($coldeps,$dep,array());
            $stack[$dep] = $this->columnProp($table,$dep,'fullname');
            $this->chaseColParents(
                $table,$coldeps,$caught,$stack,$newDeps,$colRoot,$dep
            );
        }
        
        return true;
    }
    
    /**
     * Examines structure looking for errors.
     * 
     * Each element has required properties and optional properties,
     * and required children and optional children.  This program
     * checks for all of those.
     * 
     * This program loads up the definitions.yaml file to get the
     * basic definitions.
     * 
     * Special validations are hardcoded.  One example might be
     * making sure scale is <= precision, or that columns in chain
     * definitions are inside the table.  The hardcoded validations
     * are in {@link validateSpecHardcode}.
     * 
     * Previous Step: {@link sequenceColumns}
     * 
     * Next Step: {@link validateSpecHardcode}
     * 
     * @return Boolean Always returns true
     */
    private function validateStructure() {
        # First step is to load up the definitions, this
        # gets us lists of what is allowed and required.
        $spyc = new Spyc;
        $definitions = $spyc->load($this->progdir.'definitions.yaml');
        foreach($definitions as $name=>$details) {
            foreach($details as $index=>$props) {
                if(!is_array($props)) {
                    $definitions[$name][$index] = array();
                }
            } 
        }
        $this->vsRecurse($this->spec2,'',$definitions);
        return true;
    }
        
    function vsRecurse($spec,$oChain='',$definitions) {        
        foreach($spec as $class=>$elements) {
            # Recursion correction.  Inner items will have properties,
            # the top list of items will not.
            if($class=='properties') continue;
            
            if(isset($definitions[$class]['nocheck'])) continue;
            
            # Validate the properties and then the children
            $this->vsProps($class,$oChain,$elements,$definitions);
            # KFD 5/29/09 hardcoded ignore of foreign key children
            if($class<>'foreign_keys') {
                $this->vsKids($class,$oChain,$elements,$definitions);
            }
            
            # Now drill into the child elements, if there are any,
            # and validate those.
            foreach($elements as $name=>$details) {
                $this->vsRecurse(
                    $details,$oChain.$name.'.',$definitions
                );
            }
        }
        return true;
    }
    
    private function vsProps($class,$oChain,$elements,$definitions) {
        foreach($elements as $name=>$details) {
            # grab list of element properties
            $props = $this->arr($details,'properties',array());
            
            # check for required properties, drop them
            # after locating them.
            foreach($definitions[$class]['preq'] as $pname=>$validType) {
                if(isset($props[$pname])) {
                    unset($props[$pname]);
                }
                else {
                    $this->error("Element '$oChain$name' missing"
                        ." required property '$pname'"    
                        ,'PREQ Required Property is Missing.'                   
                    );
                }
            }
            # Now check for optional properties
            foreach($definitions[$class]['popt'] as $pname=>$validType) {
                if(isset($props[$pname])) {
                    unset($props[$pname]);
                }
            }
            # Now report all remaining properties as errors
            foreach($props as $pname=>$value) {
                $this->error("Element '$oChain$name' invalid"
                    ." property '$pname'.  "
                    .$this->defineHistory($oChain.$name)
                    ,'PINV Invalid Property.'
                );                        
            }
        }
        return true;
    }
    
    private function vsKids($class,$oChain,$elements,$definitions) {
        foreach($elements as $name=>$details) {
            # Get rid of 'properties', that will confuse us
            if(isset($details['properties'])) unset($details['properties']);
            
            # check for required kids, drop them
            # after locating them.
            foreach($definitions[$class]['creq'] as $cname) {
                $child = $this->arr($details,$cname,array());
                if(count($child)> 0) {
                    unset($details[$cname]);
                }
                else {
                    $this->error("Element '$oChain$name' must have"
                        ." at least one child element"                        
                    ,'CHNO Element Must Have At Least One Child.'
                    );
                }
            }
            # Now check for optional properties
            foreach($definitions[$class]['copt'] as $cname) {
                if(isset($details[$cname])) {
                    unset($details[$cname]);
                }
            }
            # Now report all remaining children as errors
            foreach($details as $ename=>$subdetails) {
                $this->error("Element '$oChain$name' may not"
                    ." contain '$ename' elements."
                ,'CNOT Child Elements Not Allowed.'              
                );          
            }
        }
        return true;
    }
    
    /**
     * All hardcoded validation of the spec.
     * 
     * This program contains all validations that do not lend themselves
     * to data-driven approach, and are easier to deal with if we just
     * hardcode them.
     * 
     * Previous Step: {@link validateStructure}
     *
     * Next Step: {@link loadErrInfo}
     *
     * Major Helpers:
     * - {@link vsHCSequences} all validations for sequence columns
     * - {@link vsHCDupeColumns} finds columns of same name, diff specs
     * - {@link vsHCUpsave} validations for upsave
     * - {@link vsHCHistory validations for history operations
     * - {@link vsHCColumns} all column validations
     * 
     * @return Boolean Always returns true
     */
    private function validateSpecHardcode() {
        # Run through the helpers
        $this->vsHCSequences();
        $this->vsHCDupeColumns();

        # Upsave specific validations
        foreach($this->tables2 as $table=>$tdetails) {        
            $upsaves = $this->arr($tdetails,'upsaves',array());
            foreach($upsaves as $upsave=>$udetails) {
                $this->vsHCUpsave($table,$upsave,$udetails);
            }
        }
        
        # history-specific validations
        foreach($this->tables2 as $table=>$tdetails) {        
            $histories = $this->arr($tdetails,'historys',array());
            foreach($histories as $history=>$hdetails) {
                $this->vsHCHistory($table,$history,$hdetails);
            }
        }
        
        # Several column automations are done here.  You will need
        # a folding editor!  This block is long!
        foreach($this->tables2 as $table=>$tdetails) {
            $columns = $this->arr($tdetails,'columns',array());
            foreach($columns as $column=>$cdetails) {
                $this->vsHCColumns($table,$tdetails,$column,$cdetails);
            }
        }   
        return true;
    }
    

    /**
     * All validations of sequence columns
     * 
     * Helper to: {@link validateSpecHardcode}
     * 
     * @return Boolean Always returns true
     */
    private function vsHCSequences() {
        $types = array('char','varchar','int','numeric');
        foreach($this->tables2 as $table=>$tdetails) {
            $seqcols = array();
            $columns = $this->arr($tdetails,'columns',array());
            foreach($columns as $column=>$cdetails) {
                $fullname = $cdetails['properties']['fullname'];
                if($this->arr($cdetails['properties'],'auto','')=='sequence') {
                    # Record the column
                    $seqcols[$column] = $fullname;
                    
                    # Now throw an error if not in allowed types
                    $type = $cdetails['properties']['xtype'];                    
                    if(!in_array($type,$types)) {
                        $this->error("Column '$fullname' is a SEQUENCE column"
                            ." and can only be of type "
                            .' int, char, varchar, or numeric.  '
                            .$this->defineHistory($fullname)
                            ,'TPSQ Bad Type For Sequence Column.'
                        );
                    }
                }
            }
        }
    }

    /**
     * Check for columns w/same name but different type/size
     * 
     * Helper to: {@link validateSpecHardcode}
     * 
     * @return Boolean Always returns true
     */
    private function vsHCDupeColumns() {
        $coltypes = array();
        $colfulls = array();
        foreach($this->tables2 as $table=>$tdetails) {
            $columns = $this->arr($tdetails,'columns',array());
            foreach($columns as $column=>$cdetails) {
                $fullname = $cdetails['properties']['fullname'];
                $type     = $cdetails['properties']['type'];
                if(!isset($coltypes[$column])) {
                    $coltypes[$column] = $type;
                    $colfulls[$column] = $fullname;
                }
                else {
                    if(trim($type)<>trim($coltypes[$column])) {
                        $this->warning(
                            "Column '$fullname' has different type ($type) than"
                            ." previously defined column '{$colfulls[$column]}'"
                            ." of the same name ({$coltypes[$column]})."
                            ." This can be confusing to"
                            ." both programmers and end-users."
                            ." Column '{$colfulls[$column]}' "
                            .$this->defineHistory($colfulls[$column])
                            ." Column '$fullname' "
                            .$this->defineHistory($fullname)
                        );
                    }
                }
            }
        }
        return true;
    }


    /**
     * All upsave validations
     * 
     * Helper to: {@link validateSpecHardcode}
     * 
     * @return Boolean Always returns true
     */
    private function vsHCUpsave($table,$upsave,$udetails) {
        # - - - - - - - - - - - - - - - - - - - - - - - - 
        #
        # Step 1: Pop off the control properties
        #         All remaining props are column assignments
        #
        # - - - - - - - - - - - - - - - - - - - - - - - - 
        $props   = $udetails['properties'];
        $fk      = $this->arr($props,'foreign_key','');
        $flagcol = $this->arr($props,'flag','');
        $flagrst = $this->arr($props,'flag_reset','');
        $aftins  = $this->arr($props,'after_insert','');
        $aftupd  = $this->arr($props,'after_update','');
        $fullname= $this->arr($props,'fullname','');
    
        # - - - - - - - - - - - - - - - - - - - - - - - - 
        #
        # Step 2: Validate properties
        #
        # - - - - - - - - - - - - - - - - - - - - - - - - 
        if(!isset($this->tables2[$table]['foreign_keys'][$fk])) {
            $this->error(
                "Upsave '$fullname' points to undefined foreign key"
                ."'$fk'. ".$this->defineHistory($fullname)
                ,'FKNF Foreign Key Not Found.'
            );
        }
        if($aftins <> 'Y' && $aftupd <> 'Y') {
            $this->error(
                "Upsave '$fullname' does not have 'after_insert: Y'"
                ." or 'after_update: Y', nothing to do! "
                .$this->defineHistory($fullname)
                ,'UAUI Upsave Requires Insert or Update.'
            );
        }
        if($flagcol) {
            if(!$this->column($table,$flagcol)) {
                $this->error(
                    "Upsave '$fullname' names undefined flag column "
                    ."'$flagcol'. "
                    .$this->defineHistory($fullname)
                    ,'CNTF Column Not Found.'
                );
            }
            else {
                $flagtype = $this->columnProp($table,$flagcol,'xtype');
                $types = array_merge($this->numerics,$this->strings);
                if(!in_array($flagtype,$types)) {
                    $this->error(
                        "Upsave '$fullname' flag column '$flagcol' is type"
                        ." '$flagtype'.  It must be a string or number type.  "
                        .$this->defineHistory($fullname)
                        ,'TPUF Bad Type For Upsave Flag.'
                    );
                }
            }
        }
        
        if(!$this->ok()) return;
        #  <-------- EARLY RETURN
        #            If we have problems here, we cannot continue
        
        $ttab = $this->tables2[$table]
            ['foreign_keys'][$fk]['properties']['parent'];
        $tcols = $this->tables2[$ttab]['columns'];
        # The list of columns will be properties of the nested 
        # upsaves child element.  To see how this works, look
        # at debug/tables2.txt to see the array structure of
        # a table that has an upsave.
        $columns = $udetails['upsave_columnss']['upsaves']['properties'];
        $fullname = $columns['fullname'];
        # Make this go away, or we'll think its a column
        unset($columns['fullname']);
        foreach($columns as $destcol=>$srcexpr) {
            # Check the destination column, capture its type
            if(isset($tcols[$destcol])) {
                $destype = $this->columnProp($ttab,$destcol,'type');
                #$destype = $this->tables2[$ttab]['columns'][$destcol]
                #    ['properties']['type'];
            }
            else {
                $this->error(
                    "Upsave '$fullname' names undefined column '$destcol' "
                    ."in parent table '$ttab'.  "
                    .$this->defineHistory($fullname)
                    ,'CNTF Column Not Found.'
                );
                continue;
            }
            
            # Now make sure source column exists
            $srccol = '';
            if(substr($srcexpr,0,1)=='!') {
                $srccol = substr($srcexpr,1);
                if(!isset($this->tables2[$table]['columns'][$srccol])) {
                    $this->error(
                        "Upsave '$fullname' pulls undefined column '$srccol' "
                        ."to save to '$destcol'.  "
                        .$this->defineHistory($fullname)
                        ,'CNTF Column Not Found.'
                    );
                    continue;
                }
                else {
                    $srctype = $this->columnProp($table,$srccol,'type');
                    #$srctype = $this->tables2[$table]['columns'][$srccol]
                    #    ['properties']['type'];
                    if($destype <> $srctype) {
                        $this->error(
                            "Upsave '$fullname' type mismatch.  Destination"
                            ." column '$destcol' is of type '$destype', "
                            ." but source column '$srccol' is of type "
                            ." '$srctype'.  Upsave is "
                            .$this->defineHistory($fullname)
                            ,'TPXX Column Type Mismatch.'
                        );
                    }
                    continue;
                }
            }
        }
        
    }

    /**
     * All history validations
     * 
     * Helper to: {@link validateSpecHardcode}
     * 
     * @return Boolean Always returns true
     */
    private function vsHCHistory($table,$upsave,$hdetails) {
        # Get two properties we will use frequently
        $fullname = $hdetails['properties']['fullname'];
        $htable = $this->arr($hdetails['properties'],'history','');
        
        # First validation, make sure target table exists
        if(!isset($this->tables2[$htable])) {
            $this->error(
                "History '$fullname' target table not found: '$htable'. "
                .$this->defineHistory($fullname)
                ,'TNTF Table Not Found.'
            );
            return;
        }
        $hfn = $this->tables2[$htable]['properties']['fullname'];

        
        # Next validations are to make sure save_new and save_old
        # actually hit columns of same type, and that source and
        # destination exist.  But before we do that, we need to
        # get the list of saves in usable forms.
        $hnews = array();
        $holds = array();
        $hdeltas=array();
        if(isset($hdetails['hnews'])) {
            unset($hdetails['hnews']['save_new']['properties']['fullname']);
            $hnews = $hdetails['hnews']['save_new']['properties'];
        } 
        if(isset($hdetails['holds'])) {
            unset($hdetails['holds']['save_old']['properties']['fullname']);
            $holds = $hdetails['holds']['save_old']['properties'];
        } 
        if(isset($hdetails['hdeltas'])) {
            $dfullname = $hdetails['hdeltas']['deltas']['properties']['fullname'];
            unset($hdetails['hdeltas']['deltas']['properties']['fullname']);
            $hdeltas = $hdetails['hdeltas']['deltas']['properties'];
        } 
        
        
        if(count($hnews)+count($holds)+count($hdeltas)==0) {
            $this->error(
                "History '$fullname' has nothing to do!  Define at least "
                ."one 'save_new', 'save_old' or 'deltas'. "
                .$this->defineHistory($fullname)
                ,'HNON History Has Nothing To Do.'
            );
            return;
        }
        
        
        # Now lets combine and validate all three lists
        $hall = array_merge($hnews,$holds,$hdeltas);
        foreach($hall as $destcol=>$srcexpr) {
            if(!isset($this->tables2[$htable]['columns'][$destcol])) {
                $this->error(
                    "History '$fullname' saves to undefined column "
                    ."'$destcol' in target table '$htable'.  "
                    ."$fullname ".$this->defineHistory($fullname)
                    ." $htable ".$this->defineHistory($fullname)
                    ,'T_CNTF Column Not Found.'                
                );
            }
            if(substr($srcexpr,0,1)=='!') {
                $srccol = substr($srcexpr,1); 
                if(!isset($this->tables2[$table]['columns'][$srccol])) {
                    $this->error(
                        "History '$fullname' pulls undefined column "
                        ."'$srccol' to save to '$destcol'. "
                        .$this->defineHistory($fullname)
                        ,'T_CNTF Column Not Found.'                
                        );
                }
                else {
                    $dtype = $this->columnProp($htable,$destcol,'type');
                    $stype = $this->columnProp($table ,$srccol ,'type');
                    $dfn   = $this->columnProp($htable,$destcol,'fullname');
                    $sfn   = $this->columnProp($table ,$srccol ,'fullname');
                    if($dtype<>$stype) {
                        $this->error(
                            "History '$fullname' type mismatch.  Destination"
                            ." column '$destcol' is of type '$dtype', "
                            ." but source column '$srccol' is of type "
                            ." '$stype'.  Upsave is "
                            .$this->defineHistory($fullname)
                            ." Destination column is "
                            .$this->defineHistory($dfn)
                            ." Source column is "
                            .$this->defineHistory($sfn)
                            ,'TPXX Column Type Mismatch.'
                        );           
                    }
                }
            }
        }
        
        # We have a separate validation for deltas, they must all
        # be column expressions and numerical.
        foreach($hdeltas as $destcol=>$srcexpr) {
            if(substr($srcexpr,0,1)!='!') {
                $this->error(
                    "History '$fullname' delta expression '$srcexpr' "
                    ."must name a column, literals are not supported. "
                    ." Deltas ".$this->defineHistory($dfullname)
                    ,'HDCL History Delta Must Be a Column.'
                );
                continue;
            }
            $srccol = substr($srcexpr,1);
            $dtype = $this->columnProp($table,$srccol,'xtype');
            if(!in_array($dtype,$this->numerics)) {
                $srccol = substr($srcexpr,1);
                $this->error(
                    "History '$fullname' delta column '$srccol' "
                    ."must be a number type."
                    ." Deltas ".$this->defineHistory($dfullname)
                    ,'TPHD Bad Type For History Delta.'
                );
                continue;
            }
        }

        # See if there is a char column "action" in 
        # destination table
        $action = false;
        if(isset($this->tables2[$htable]['columns']['action'])) {
            $xtype=$this->columnProp($htable,'action','xtype');
            $xprec=$this->columnProp($htable,'action','xprecision');
            $xfn  =$this->columnProp($htable,'action','fullname');
            if(!in_array($xtype,array('char','varchar'))) {
                $this->error(
                    "History '$fullname' destination table '$htable'"
                    ." has an 'action' column which must be type"
                    ." 'char(7)' or 'varchar(7)'.  Action column "
                    .$this->defineHistory($xfn)
                    ,'HACT Bad Type For History Action Column.'
                );
            }
            else {
                if($xprec < 7) {
                    $this->error(
                        "History '$fullname' destination table '$htable'"
                        ." has an 'action' column which is too small,"
                        ." it should be 'char(7)' or 'varchar(7)."
                        ."  Action column ".$this->defineHistory($xfn)
                        ,'HACS History Action Column Too Small.'
                    );
                }
                else {
                    $action = true;
                }
            }
        } 
        
        return true;
    }

    /**
     * All column validations not handled elsewhere
     * 
     * Helper to: {@link validateSpecHardcode}
     *
     * Major validations:
     * - cannot have automation and default on same column
     * - timestamp autos column must be type timestamp
     * - userstamp must be char/varchar
     * - (warning) userstamp ought to be > 15
     * - DOMINANTS must be string or number
     * - If DOMINANT names foreign key, must be valid
     * 
     * @return Boolean Always returns true
     */
    private function vsHCColumns($table,$tdetails,$column,$cdetails) {
        # Get some good stuff for the column
        $auto       = $this->arr($cdetails['properties'],'auto');
        $xauto      = $this->arr($cdetails['properties'],'xauto');
        $xautotable = $this->arr($cdetails['properties'],'xautotable');
        $fk         = $xautotable;
        $xautocolumn= $this->arr($cdetails['properties'],'xautocolumn');
        $default    = $this->arr($cdetails['properties'],'default');
        $fullname   = $cdetails['properties']['fullname'];
        $xtype      = $cdetails['properties']['xtype'];
        
        # TEST: Automation and default together are error
        if($auto<>'' && $default<>'') {
            $this->error(
                "Column '$fullname' has both 'default' property"
                ." and an automation.  The automation will overwrite"
                ." the default.  Please remove the default. "
                .$this->defineHistory($fullname)
                ,'PEXC Mutually Exclusive Properties.'
            );                    
        }   
        
        # TEST: Automation timestamp must be, of course, a timestamp
        $x = array('ts_ins','ts_upd','ts_ui');
        if(in_array($xauto,$x) && $xtype <>'datetime') {
            $this->error(
                "Column '$fullname' must be of type 'timestamp' to support "
                ." automation '$auto'. "
                .$this->defineHistory($fullname)
                ,'E_TPTS Bad Type For Timestamp Column.'
            );
        }
        
        # Userstamp columns must be char/varchar
        # 
        $x = array('user_ins','user_upd','user_ui');
        if(in_array($xauto,$x) && ($xtype!='char' && $xtype!='varchar')) {
            $this->error(
                "Column '$fullname' has a userstamp automation '$xauto' but"
                ." is not type 'char' or 'varchar'. "
                .$this->defineHistory($fullname)
                ,'TPUS Bad Type For Userstamp Column.'
            );
            return;
        }
        if(in_array($xauto,$x) && $cdetails['properties']['xprecision']<15) {
            $xp = $cdetails['properties']['xprecision'];
            $this->warning(
                "Column '$fullname' is only $xp characters, this may produce"
                ." unexpected runtime errors if users have long usernames. "
                .$this->defineHistory($fullname)
            );
        }
        
        # DOMINANT columns must be string or number
        $x = array_merge($this->strings,$this->numerics);
        if($xauto == 'dominant' && !in_array($xtype,$x)) {
            $this->error(
                "Column '$fullname' has automation DOMINANT, it "
                ." must be a string or number type.  "
                .$this->defineHistory($fullname)
                ,'TPDM Bad Type for Dominant Column.'
            );
        }

        # DOMINANT columns must name valid foreign key, if named
        #          at all
        if($xauto == 'dominant' && $fk<>'') {
            if(!isset($this->tables2[$table]['foreign_keys'][$fk])) {
                $this->error(
                    "Column '$fullname' has automation DOMINANT "
                    ."filtered on non-existent foreign key: $fk. "
                    .$this->defineHistory($fullname)
                    ,'FKNF Foreign Key Not Found.'
                );
            }
        }

        # QUEUEPOS must be numeric
        if($xauto == 'queuepos' && !in_array($xtype,$this->numerics)) {
            $this->error(
                "Column '$fullname' has automation QUEUEPOS, it "
                ." must be a number type.  "
                .$this->defineHistory($fullname)
                ,'TPQP Bad Type For Queuepos Column.'
            );
        }    
        # QUEUEPOS, if naming foreign key, fk must be valid
        if($xauto == 'queuepos' && $fk <> '') {
            if(!isset($this->tables2[$table]['foreign_keys'][$fk])) {
                $this->error(
                    "Column '$fullname' has automation QUEUPOS "
                    ."filtered on non-existent foreign key: $fk. "
                    .$this->defineHistory($fullname)
                    ,'FKNF Foreign Key Not Found.'
                );
            }
        }
            

        # Aggregation validations
        if(in_array($xauto,array('sum','count','min','max','latest'))) {
            # Sum and count must be numerics
            $nums = array('sum','count');
            if(in_array($xauto,$nums)) {
                if(!in_array($xtype,$this->numerics)) {
                    $this->error(
                        "Column '$fullname' has automation '$xauto' "
                        ."but is not a number type. "
                        .$this->defineHistory($fullname)
                    ,'TPAG Bad Type For Aggregation.'
                    );
                }                    
            }
            
            # 3 TESTS: Bad fk stuff
            if($xautotable=='') {
                $this->error(
                    "Column '$fullname' has automation '$xauto' "
                    ."but does not name a foreign key or column. "
                    .$this->defineHistory($fullname)
                    ,'FKRQ Foreign Key Required.'
                );                    
            }
            if($xautocolumn=='' && $xauto<>'count') {
                $this->error(
                    "Column '$fullname' has automation '$xauto' "
                    ."but does not name a source column. "
                    .$this->defineHistory($fullname)
                    ,'CREQ Column Required.'
                );                    
            }
            if(!isset($tdetails['fk_children'][$fk])) {
                $this->error(
                    "Column '$fullname' with automation '$xauto' "
                    ."names undefined foreign key '$fk'. "
                    .$this->defineHistory($fullname)
                    ,'FKNF Undefined Foreign Key.'
                );                    
            }
            else {
                # If there is a good foreign key, we can start
                # looking for column mismatches.
                
                # First: column does not exist
                $ctable = $tdetails['fk_children'][$fk]['child'];
                $bool1 = isset(
                    $this->tables2[$ctable]['columns'][$xautocolumn]
                );
                $bool2 = $xauto=='count' ? true : false;
                if(!$bool1 && !$bool2) {
                    $this->error(
                        "Column '$fullname' with automation '$xauto' "
                        ." pulls from undefined column "
                        ."'$ctable.$xautocolumn'. "
                        .$this->defineHistory($fullname)
                        ,'CNTF Column Not Found.'
                    );    
                    # <<<<----------- RETURN
                    return;
                    # <<<<----------- on to next column                
                }
                
                # Second: SUM, source is not numeric
                $xtsr = $this->columnProp(
                    $ctable,$xautocolumn,'xtype'
                );
                $xcfn = $this->columnProp(
                    $ctable,$xautocolumn,'fullname'
                );
                if($xauto=='sum' && !in_array($xtsr,$this->numerics)){
                    $this->error(
                        "Column '$fullname' sums from non-numeric"
                        ." column '$xcfn'. "
                        .$this->defineHistory($fullname)
                        ."  Source column "
                        .$this->defineHistory($xcfn)
                        ,'TPAG Bad Type For Aggregation.'
                        
                    );                    
                }
                $xtypematch=array('min','max','latest');
                if(in_array($xauto,$xtypematch)) {
                    $ptype = $this->columnProp(
                        $table,$column,'type'
                    );
                    $ctype = $this->columnProp(
                        $ctable,$xautocolumn,'type'
                    );
                    if($ptype<>$ctype) {
                        $this->error(
                        "Column '$fullname' with automation '$xauto', "
                        ." type '$ptype' does not match type, "
                        ."precision and scale with source column "
                        ."'$xcfn' type '$ctype'.  "
                        .$this->defineHistory($fullname)
                        ."  Source column "
                        .$this->defineHistory($xcfn)
                        ,'TPXX Column Type Mismatch.'
                        );
                    }
                }
            }
        }
    }
    
    # =============================================================
    #
    #  REGION: By-Column CODE GENERATION
    #
    # =============================================================
    
    /**
     * Loads file errinfo.yaml to {@link errInfo} array.
     * 
     * For more information on the error Information, see the
     * header comments in errinfo.yaml.
     * 
     * Previous Step: {@link validateSpecHardcode}
     * 
     * Next Step: {@link loadSprocs}
     * 
     * @return Boolean Always returns true
     */
    private function loadErrInfo() {
        $spyc = new Spyc();
        $this->errInfo = $spyc->load("errinfo.yaml"); 
        return true;
    }
    
    /**
     * Loads the triAPI.*.sql files into the build plan at the 
     * 
     * Previous Step: {@link loadErrInfo}
     * 
     * Next Step: {@link codeByColumns}
     *
     * Helper platform-specific: {@link loadSprocsPlatform}
     * 
     * @return Boolean Always returns true
     */
    private function loadSprocs() {
        $file = "triAPI.{$this->platform}.sql";
        if(!file_exists($file)) {
            $this->error("Platform API file $file not found. "
                ." This is most likely an problem with your install."
            );
        }
        
        # Get file, split off header from body, convert
        # carriage returns to newlines, then split
        # body into individual entries
        $fileAsString = file_get_contents($file);
        $fileAsString = str_replace("\r","\n",$fileAsString);
        $fileAsString = str_replace("\n\n","\n",$fileAsString);
        list($header,$fileBody) = explode(
            "----- FILE BEGINS BELOW THIS LINE -----"
            ,$fileAsString
        );
        $functions = explode("-- FUNCTION --",$fileBody);
        
        # Now we process each entry.  We skip all blank lines
        foreach($functions as $function) {
            # PHP trim() kills newlines, tabs, etc., very useful
            $function = trim($function);
            
            # Split into lines, get the name, and reconstruct
            # the body
            $functionLines = explode("\n",$function);
            $name = array_shift($functionLines);
            $function = implode("\n",$functionLines);
            
            # If this is a *NATIVE* function, don't do anything
            if(trim($function)=='*NATIVE*') continue;
            
            $this->builds['sprocs'][]=$this->loadSprocPlatform($name,$function)."\n\n";
        }
        return true;
    }
    /**
     * Platform-specific API Sproc reformatter
     *
     * Helper to {@link @loadSproc}
     * 
     * @return Boolean Always returns true
     */
    private function loadSprocPlatform($name,$function) {
        return $function;    
    }
    
    /**
     * Goes through every table and generates non-trigger code
     *
     * This is a complement to {@link generateTriggers}.  This
     * routine generates extra snippets that go into column
     * create text for table create commands.
     *
     * This is part of a two-path coding solution.  Some features
     * can be done as declarations on columns on some platforms,
     * but may require trigger code on other platforms.  Therefore
     * any particular feature might be implemented in this path
     * or in the {@link generateTriggers} path.
     *
     * Helpers:
     * - {@link columnMakeDefault}
     * - {@link columnMakeSequencePlatform}
     *
     * Previous Step: {@link loadSprocs}
     * 
     * Next Step: {@link writeScript}
     *
     * @return Boolean Always returns true
     */
    private function codeByColumns() {
        # First we do the unsequenced assignments, like
        # defaults, stamping, and ids
        foreach($this->tables2 as $table=>$tdetails) {
            $props = $tdetails['properties'];
            
            # Dispatch for each column based on its automation
            foreach($tdetails['columns'] as $column=>$cdetails)  {
                $props = $cdetails['properties'];
                $xauto = $this->arr($props,'xauto','');
                switch($xauto) {
                    case '':
                        $default = $this->arr($cdetails['properties'],'default');
                        if($default<>'') {
                            $this->columnMakeDefault($table,$column,$cdetails);
                        }
                        break;
                    case 'sequence':
                        $this->columnMakeSequencePlatform($table,$column,$cdetails);
                        break;
                }
            }
        }    
    }    
    
    /**
     * Generates code for default assignments.
     *
     * Cross-platform, no overrides supported.  Uses ANSI
     * SQL standard DEFAULT clause on column definition
     *
     * Helper to {@link codeByColumns}
     * 
     * @param $table
     * @param $column
     * @param $cdetails
     * @return Boolean Always returns true
     */
    private function columnMakeDefault($table,$column,$cdetails) {
        # We always need the expression
        $xdefault = $this->arr($cdetails['properties'],'default','');
        $xtype = $cdetails['properties']['xtype'];
        $expr = $this->sqlFormat($xtype,$xdefault);
        $this->tables2[$table]['columns'][$column]['properties']['extra'] 
            = " DEFAULT $expr";

        return true;
    }  
    
    
    /**
     * Placeholder for platform-specific sequence
     *
     * Default behavior is to do nothing
     *
     * Helper to {@link codeByColumns}
     * 
     * @param $table
     * @param $column
     * @param $cdetails
     * @return Boolean Always returns True
     */
    protected function columnMakeSequencePlatform($table,$column,$cdetails) {
        return true;
    }  
    
    # =============================================================
    #
    #  REGION: By-Table CODE GENERATION (tables, indexes, keys)
    #
    # =============================================================
    

    /**
     * Generate the Build/Update Script for tables
     *
     * THIS EXPLANATION IS COMPLETELY WRONG AND NEEDS TO BE
     * REWRITTEN.  ALL THAT THIS ROUTINE DOES IS CREATE TABLES
     * AND COLUMNS
     * 
     * This program loops through the specification and generates
     * a script that will build or upgrade the database.  
     * 
     * The program generates script snippets and slots them into
     * the array {@link builds}.
     * The top keys of those arrays
     * specify the supported platforms:
     * - 'postgres' for PostgreSQL
     * - 'mssql' for Microsoft SQL Server
     * - 'oracle' for Oracle
     * - 'mysql' for mySql
     * 
     * The array for each platform is initialized with the
     * following keys.  The final script is generated by
     * running through the keys in order, and then running
     * through the individual snippets within a key in order.
     * The keys are:
     * 
     * - init: database wide init commands
     * - tables: table create or update commands
     * - skeys: commands to create auto-increment fields
     * - indexes: commands to create indexes
     * - triggers: commands to create or update triggers
     * - functions: commands to create or alter functions
     * 
     * The program and its helpers also generates trigger fragments and slot
     * them into the {@link triggers} array.  More information on that array
     * is available at {@see triggerFragment}.  
     * See also {@link triggersAssemble}.
     * 
     * All helpers to this routine begin with "script".  All helpers
     * write directly to the {@link builds} and
     * {@link triggers} arrays.
     *
     * Previous Step: {@link assembleTriggers}
     *
     * Nextg Stgep: {@link writeDictionary}
     *
     * 
     * @return Boolean Always returns true
     */
    private function writeScript() {
        foreach($this->keySequence as $table) {
            $this->scriptCreateTable($table);
        }
        return true;
    }
    
    /**
     * Generate CREATE TABLE commands.
     * 
     * This program generates a complete CREATE TABLE command,
     * as well as any associated commands to assign ownership.
     * This program generates no other script, all other items
     * like triggers, indexes and keys are generated in their
     * own routines.
     * 
     * This program directly writes to the {@link builds} array.
     * The commands are put into the 'tables' subkey.
     * 
     * @param $tdetails A table specification in {@link spec2} format
     * @param $table The name of the table to build
     * @param $tdetails Table definition details
     * @return Boolean Always returns true
     */
    private function scriptCreateTable($table) {
        $tdetails = $this->tables2[$table];
        # The "type" properties have all been validated, so
        # we can just use them as they were entered.
        $cols = array();
        foreach($tdetails['columns'] as $column=>$cdetails) {
            $cols[]
                =str_pad($column                                ,18,' ',STR_PAD_RIGHT)
                .str_pad($cdetails['properties']['typeplatform'],15,' ',STR_PAD_RIGHT)
                .$this->arr($cdetails['properties'],'extra','');
        }

        # Build the command
        $this->builds['tables'][] 
            ="CREATE TABLE $table (\n"
            ."    ".implode("\n   ,",$cols)
            ."\n"
            .") "
            .$this->platformTableExtra;

        # Add in the keys
        $keys = $this->arr($tdetails,'keys',array());
        foreach($keys as $keyName=>$kdetails) {
            $cols = implode(",",$kdetails['keycols']);
            $name = $keyName=='primary_key' ? 'PRIMARY KEY' : 'UNIQUE';
            $this->builds['tables'][]
                ="ALTER TABLE $table ADD CONSTRAINT {$table}_$keyName $name ($cols);\n";
        }

        # Add in the indexes
        $idxs = $this->arr($tdetails,'indexes',array());
        foreach($idxs as $idxName=>$idetails) {
            $cols = implode(",",$idetails['idxcols']);
            $this->builds['tables'][]
                ="CREATE INDEX idx_{$table}_{$idxName} ON $table ($cols);\n";
        }


        # Add in the foreign keys
        $fks = $this->arr($tdetails,'foreign_keys',array());
        foreach($fks as $fkname=>$fkdetails) {
            $this->builds['tables'][]
                ="ALTER TABLE $table"
                ." ADD CONSTRAINT fk_{$table}_{$fkname} FOREIGN KEY"
                ." (".implode(',',$fkdetails['properties']['colschd']).")"
                ." REFERENCES ".$fkdetails['properties']['parent']
                ." (".implode(',',$fkdetails['properties']['colspar']).");\n";
        }

        return true;
    }
    
    # =============================================================
    #
    #  REGION: Trigger generation, all logic not in table definitions
    #
    # =============================================================
    
    
    /**
     * Goes through every table and generates all non-table code.
     * 
     * This routine generates trigger code for all features that
     * are handled by triggers.   
     * 
     * The different abilities of different platforms often make
     * this job very difficult.  Some features are handled so 
     * differently on each platform that things can become very
     * confusing.  To deal with this, the program uses this
     * strategy:
     * - A particular feature such as SUM or SEQUENCE is always
     *   handled entirely inside its own routine.  Code to implement
     *   a feature is never spread out into different areas.  All
     *   variations for all platforms are inside that one routine
     *   (or maybe its helpers).
     * - While most features can be implemented in triggers, a feature
     *   like SEQUENCE is handled in triggers on one platform and as
     *   part of the column definition in others.  In these cases, the
     *   routine that implements triggers will make additions to the
     *   {@link spec2} array that will be handled thoughtlessly by
     *   table-building code.
     *   
     * 
     * Previous Step: {@link loadSprocs}
     * 
     * Next Step: {@link assembleTriggers}
     * 
     * Helper: {@link triggerFragment}
     * 
     * Major top-line helpers:
     * - {@link generateTriggersUnSequenced} defaults, stamps, ids,
     *   and everything else where order of events does not matter
     * - {@link generateTriggersSequenced} fetches and calculations
     * - {@link generateTriggersRest} the rest of it
     * 
     * Other Helpers to make particular features:
     * - {@link triggerDefaultPlatform} Codes up default assignments
     * - {@link triggerSequencePlatform} Codes up sequence automations
     * - {@link triggerTimestampPlatform} Codes all three timestamp automations
     * - {@link makeUserstamp} Codes all three userstamp automations
     * - {@link makeDominant} Codes up dominant operations
     * - {@link makeQueuepos} Codes up queuepos operations
     * - {@link triggerAggregations} Codes up aggregrations, using
     *   platform-specific helper {@link triggerAggregationsPlatform}
     * - {@link triggerFetches} Codes up fetches, using
     *   platform-specific helper {@link triggerFetchesPlatform}
     * - {@link makeUpsave} Codes upsave operations
     * - {@link makeHistory} Codes history operations
     * 
     * @return Boolean Always returns true
     */
    private function generateTriggers() {
        $this->generateTriggersUnSequenced();
        $this->generateTriggersSequenced();
        $this->generateTriggersRest();
    }
    
    private function generateTriggersUnsequenced() {
        # First we do the unsequenced assignments, like
        # defaults, stamping, and ids
        foreach($this->tables2 as $table=>$tdetails) {
            $props = $tdetails['properties'];
            
            # Dispatch for each column based on its automation
            foreach($tdetails['columns'] as $column=>$cdetails)  {
                $props = $cdetails['properties'];
                $auto = $this->arr($props,'xauto','');
                switch($auto) {
                    case '':
                        $default = $this->arr($cdetails['properties'],'default');
                        if($default<>'') {
                            $this->triggerDefaultPlatform($table,$column,$cdetails);
                        }
                        break;
                    case 'sequence':
                        $this->triggerSequencePlatform($table,$column,$cdetails);
                        break;
                    case 'gsequence':
                        $this->triggerSequencePlatform($table,$column,$cdetails,true);
                        break;
                    case 'ts_ins':
                    case 'ts_upd':
                    case 'ts_ui':
                        $this->triggerTimestampPlatform($table,$column,$cdetails,$auto);
                        break;
                    case 'user_ins':
                    case 'user_upd':
                    case 'user_ui':
                        $this->makeUserstamp($table,$column,$cdetails,$auto);
                        break;
                    case 'dominant':
                        # KFD Turn off dominants, they are too much
                        # trouble until other things are
                        # straightened out
                        $this->makeDominant($table,$column,$cdetails);
                        break;
                    case 'queuepos':
                        # KFD turn off queuepos, they are too much 
                        # trouble until other things are straightened
                        # out
                        $this->makeQueuepos($table,$column,$cdetails);
                        break;
                }
            }
        }
    }
    
    private function generateTriggersSequenced() {
        foreach($this->colSequence as $table=>$actions) {
            foreach($actions as $name=>$type) {
                if($type=='foreign_key') {
                    $fkdetails = $this->tables2[$table]['foreign_keys'][$name];
                    $this->triggerFetches($table,$fkdetails);
                }
                else {
                    $calc = $this->columnProp($table,$name,'calculate');
                    $this->triggerTranslate($table,'i','calcs',$calc);                
                    $this->triggerTranslate($table,'u','calcs',$calc);                
                }
            }
        }
    }
        
        
        
    private function generateTriggersRest() {
        foreach($this->tables2 as $table=>$tdetails) {
            # Now look at the foreign keys to see which
            # automations they have
            $fks = $this->arr($tdetails,'foreign_keys',array());
            foreach($fks as $fk=>$fkdetails) {
                if(isset($fkdetails['autos'])) {
                    $this->triggerAggregations($table,$fkdetails);
                }
            }

            # If there are any upsaves for the table, do them now
            $upsaves = $this->arr($tdetails,'upsaves',array());
            foreach($upsaves as $upsave=>$udetails) {
                $this->makeUpsave($table,$upsave,$udetails);
            }

            # If there are histories for the table, do them now
            $histories = $this->arr($tdetails,'historys',array());
            foreach($histories as $history=>$hdetails) {
                $this->makeHistory($table,$history,$hdetails);
            }

            /*
            # Simple, get the primary key slotted
            if($props['primary_key'] <> '') {
                $this->triggerFragment($table,'i','pk','Y');
                $this->triggerFragment($table,'u','pk','Y');
            }
            
            # Now go through foreign keys
            $fks = $this->arr($tdetails,'foreign_keys',array());
            foreach($fks as $fk=>$fkdetails) {
                # First thing is list the fk itself as needing
                # to be checked in child table
                $this->triggerFragment(
                    $table,'i','fk',$fkdetails['properties']['fullname']
                );
                $this->triggerFragment(
                    $table,'u','fk',$fkdetails['properties']['fullname']
                );
                
                # Now work out what to tell the parent's delete,
                # whether restrict or cascade
                $del = $this->arr($fkdetails['properties'],'delete','restrict');
                $par = $fkdetails['properties']['parent'];
                if($del=='restrict') {
                    $this->triggerFragment(
                        $par,'d','fkrest',$fkdetails['properties']['fullname']
                    );
                }
                else {
                    $this->triggerFragment(
                        $par,'d','fkcasc',$fkdetails['properties']['fullname']
                    );
                }
            }
            */

            
        }
        return true;
    }

    /**
     * Accepts a trigger fragment and slots it into {@link triggers}.
     * 
     * When this routine accepts a fragment, it first checks to see
     * if any fragments have been received for this particular
     * table and event combination.
     * If none have been received, it initializes an empty array for
     * that combination, then slots the text appropriately.  
     *
     * Trigger fragments are slotted into {@link triggers}.  All of the
     * fragments in this array are assembled into complete scripts
     * that are added to {@link builds} by
     * {@link triggersAssemble}.
     * 
     * The complete key structure is:
     * - each table
     *   - Update, insert, or delete
     *     - section
     *     - section
     *     - section
     *     
     * See the source for individual section details.
     *           
     * The individual platform-specific builders make the decisions
     * about BEFORE/AFTER, ROW/STATEMENT etc.
     * 
     * This routine also creates the arrays 'tbefore' and 'tafter',
     * which list the sections that go into BEFORE and AFTER
     * triggers, respectively.
     *        
     * @param $table string The table name
     * @param $uid (u)pdate or (i)nsert or (d)elete
     * @param $section See above
     * @param $fullname An element name out of {@link spec2}
     * @param $info information about the action
     * @return Boolean Always Returns True
     */
    protected function triggerFragment($table,$uid,$section,$snippet) {
        # Explode the trigger fragment and move all DECLARE
        # lines over to the DECLARES array, they are moved up
        # to the top of each trigger
        $lines = explode("\n",$snippet);
        foreach($lines as $idx=>$line) {
            if( substr(strtolower($line),0,7)=='declare') {
                unset($lines[$idx]);
                $this->declares[$table][$uid][] = $line;
            }
        }
        $snippet = implode("\n",$lines);
        
        # Slot the trigger fragment
        $this->triggers[$table][$uid][$section][] = $snippet;

        return true;
    }
    
    /**
     * Takes a snippet of Universal code and translates to various platforms.
     * 
     * This routine takes a snippet of univeral Triangulum trigger code
     * and translates it into platform-specific code.
     * 
     * The fourth parameter is a comma-delimited list of platforms.
     * If it is blank all platforms are processed.  If the list begins
     * with an exclamation point, then it is a list of platforms to
     * exclude.
     * 
     * @param $table The table name
     * @param $uid (u)pdate, (i)nsert or (d)elete
     * @param $section A trigger section, see {@triggerFragment} source
     * @param $platforms list of platforms, see above
     * @return Boolean Always returns true
     */
    protected function triggerTranslate($table,$uid,$section,$code){
        echo "\nHere we are in base trigger translate\n";
        $platformCode = $this->codeTranslate($code);
        $this->triggerFragment(
            $table,$uid,$section,$platformCode
        );
        
        return true;
    }
    
    /**
     * Generates code for default assignments.
     * 
     * Currently blank, does nothing because we have defauls
     * being generated in table code.  Can be overridden
     * in platform-specific class.
     *
     * @param $table
     * @param $column
     * @param $cdetails
     * @return Boolean Always returns true
     */
    protected function triggerDefaultPlatform($table,$column,$cdetails) {
        /*
        # We always need the expression
        $xdefault = $cdetails['properties']['default'];
        $xtype = $cdetails['properties']['xtype'];
        $expr = $this->sqlFormat($xtype,$xdefault);

        $uss="{IF} @new_$column IS NULL {THEN}\n"
            ."    {SET} @new_$column = $expr;\n"
            ."{ENDIF}\n";
        $this->triggerTranslate($table,'i','calcs',$uss);
        */
        
        return true;
    }
    
    /**
     * Generates data in appropriate arrays for sequence columns
     *
     * NOTUSED?  This can go away when sequence works
     * 
     * This routine creates any trigger fragments necessary and 
     * adds properties to the spec where necessary to get 
     * sequence columns working.
     * 
     * This program directly writes to the {@link specExtras} array.
     * 
     * This routine directly does what is necessary for all platforms,
     * there are no case statements, loops, or conditionals.
     * 
     * Calls: {@link triggerFragment}
     * 
     * @param $table
     * @param $column
     * @param $cdetails
     * @return Boolean Always returns True
     */
    protected function triggerSequencePlatform($table,$column,$cdetails,$global=false) {
        # Get these parameters set aside for future use
        $fullname = $cdetails['properties']['fullname'];
        $xtype    = $cdetails['properties']['xtype'];
        $xprec    = $cdetails['properties']['xprecision'];
        $insert   = $this->arr($cdetails['properties'],'insert','Y');
        $update   = $this->arr($cdetails['properties'],'update','Y');
        $zfill    = $this->arr($cdetails['properties'],'zerofill','N');
        $fill     = $zfill=='Y' ? "'0'" : "' '";
        if($global) {
            $seqtab = 'sequence_global';  
            $seqcol = 'sequence_global';
        }
        else {
            $seqtab = "{$table}_{$column}_seq";
            $seqcol = $column;
        }

        # Define the two errors, these are inserts and updates
        # when column is not allowed to have inserts or updates.
        $ierr ="{TRI:10100:$table:$column}";
        $ierru="{ERROR:10100:$table:$column}";
        $uerr ="{TRI:10200:$table:$column}";
        $uerru="{ERROR:10200:$table:$column}";
        
        # >>>>>>>        
        # >>>>>>> CASE MSSQL
        # >>>>>>> CASE MYSQL
        # >>>>>>>

        # Now make the extra table.  The conditional prevents
        # the global table from being created more than once
        /*
        $plats = array('mssql'=>'identity(1,1)','mysql'=>'auto_increment');
        foreach($plats as $plat=>$platspec) {
            # Sequence columns must always be unique
            $this->specExtra[$plat][$fullname]['coldefine']=" UNIQUE";
            if(!isset($this->builds[$plat]['tables'][$seqtab])) {
                $this->builds[$plat]['tables'][$seqtab]
                    ="CREATE TABLE $seqtab (\n"
                    ."  $seqcol int not null $platspec UNIQUE\n"
                    ." ,filler char(1)"
                    .");";
            }
        }
        */
            
        # The insert version has to have switches for column
        # type and whether or not they are allowed to make
        # inserts.
        $sqlinsert = '';
        if($xtype=='numeric' || $xtype=='int') {
            $setid = "{SET} @new_$column = triIdentity();\n";
            $setins= "";
        }
        else {
            $setid_pre = "TRIM(CAST(triIdentity() as char($xprec)))";
            $setid = "{SET} @new_$column = LPAD($setid_pre,$xprec,$fill);";
            $setins
                ="{SET} @new_$column = LPAD(TRIM(@new_$column),$xprec,$fill);"
                ."\n";
        }
        if($insert=='N') {
            $sqlinsert="    {ERROR:10100:$table:$column}\n";
        }
        else {
            $sqlinsert
                ="    {MSSQL}SET IDENTITY_INSERT $seqtab ON;\n"
                ."    INSERT INTO $seqtab ($seqcol)\n"
                ."           VALUES (@new_$column);\n"
                ."    $setins"
                ."    {MSSQL}SET IDENTITY_INSERT $seqtab OFF;\n";
        }
        $trigger
            ="{IF} (@new_$column IS NOT NULL) {THEN}\n"
            .$sqlinsert
            ."{ELSE}\n"
            ."    INSERT INTO $seqtab (filler) values ('X');\n"
            ."    $setid\n"
            ."{ENDIF}\n"
            ."DELETE FROM $seqtab WHERE $seqcol = \n"
            ."       CAST(@new_$column as {TYPEINT});\n";
        $this->triggerTranslate($table,'i','calcs',$trigger,'!postgres');
        
        # The update version only does something if the value changed.
        if($update=='N') {
            $trigger
                ="{IF} (@new_$column <> @old_$column) {THEN}\n"
                ."    $uerru\n"
                ."{ENDIF}\n";
        }
        else {
            $trigger
                ="{IF} (@new_$column <> @old_$column) {THEN}\n"
                ."    {MSSQL}SET IDENTITY_INSERT $seqtab ON;\n"
                ."    INSERT INTO $seqtab ($seqcol)\n"
                ."           VALUES (@new_$column);\n"
                ."    $setins"
                ."    {MSSQL}SET IDENTITY_INSERT $seqtab OFF;\n"
                ."{ENDIF}\n";
        }
        $this->triggerTranslate($table,'u','calcs',$trigger,'!postgres');
        
        # >>>>>>>        
        # >>>>>>> CASE POSTGRES
        # >>>>>>>
        
        # Make sure the column has a unique constraint
        $this->specExtra['postgres'][$fullname]['coldefine']
            =" UNIQUE";
            
        # Build the sequence.  Conditional prevents repeat
        # commands for global sequences.
        /*
        if(!isset($this->builds['postgres']['tables'][$seqtab])) {
            $this->builds['postgres']['tables'][$seqtab]
                ="CREATE SEQUENCE $seqtab "
                ."START WITH 1 INCREMENT BY 1;\n";
        }
        */
        $expr ="TRIM(cast(nextval('$seqtab') as $xtype($xprec)))";
        $expr2="TRIM((new.$column::int)::char($xprec))";
        $fill= ($zfill=='Y') ? "'0'" : "' '";
        if($insert=='N') {
            $sqlinsert="    ".$this->genError('postgres',$ierr);
        }
        else {
            $sqlinsert
                ="IF(new.$column::int >= (select last_value from $seqtab)) THEN\n"
                ."    PERFORM SETVAL('$seqtab',new.$column::int+1,false);\n"
                ."END IF;";
        }
        if($xtype=='int' || $xtype=='numeric') {
            $trigger
                ="IF new.$column IS NULL THEN\n"
	            ."    new.$column = nextval('$seqtab');"
	            ."ELSE\n"
                .$this->indent($sqlinsert,4)."\n"
	            ."END IF;\n";
        }
        else {
            $trigger
                ="IF new.$column IS NULL THEN\n"
                ."    new.$column = LPAD($expr,$xprec,$fill);"
                ."ELSE\n"
                .$this->indent($sqlinsert,4)
	            ."    new.$column = LPAD($expr2,$xprec,$fill);"
	            ."END IF;\n";
        }
        $this->triggerFragment(
            $table,'i','calcs',$trigger
        );
        if($update=='N') {
            $trigger
                ="IF new.$column <> old.$column THEN\n"
                .$this->genError('postgres',$uerr)
                ."END IF;\n";
        }
        else {
            if($xtype=='int' || $xtype=='numeric') {
                $trigger
                    ="IF new.$column <> old.$column THEN\n"
                    .$this->indent($sqlinsert,4)
                    ."END IF;\n";
            }
            else {
                $trigger
                    ="IF new.$column <> old.$column THEN\n"
                    .$this->indent($sqlinsert,4)."\n"
                    ."    new.$column = LPAD($expr2,$xprec,$fill);\n"
                    ."END IF;\n";
            }
        }
        $this->triggerFragment($table,'u','updcons',$trigger);
        return true;
    }
    

    /**
     * Codes up timestamp automations.
     *
     * Empty, expects to be overridden in platform-specific child classes.
     * 
     * @param $table Table Name
     * @param $column Column Name
     * @param $cdetails Column Details
     * @param $auto Automation code
     * @return Boolean Always Returns True
     */
    protected function triggerTimestampPlatform($table,$column,$cdetails,$auto) {
        # We have some error trapping here, checking for
        # timestamps on non-datetime columns
        
        /*
        # >>>>>>> CASE MSSQL
        if($auto=='ts_ins' || $auto=='ts_ui') {            
            $trigger="SET @new_$column = GetDate()\n";
            $this->triggerFragment($table,'i','calcs',$trigger);
        }
        if($auto=='ts_upd' || $auto=='ts_ui') {            
            $trigger="SET @new_$column = GetDate()\n";
            $this->triggerFragment($table,'u','calcs',$trigger);
        }
            
        # >>>>>>> CASE MYSQL
        if($auto=='ts_ins' || $auto=='ts_ui') {            
            $trigger="SET new.$column = NOW();\n";
            $this->triggerFragment($table,'i','calcs',$trigger);
        }
        if($auto=='ts_upd' || $auto=='ts_ui') {            
            $trigger="SET new.$column = NOW();\n";
            $this->triggerFragment($table,'u','calcs',$trigger);
        }
        */
        return true;
    }
    
    /**
     * Codes up userstamp automations.
     *
     * Not yet converted to base/override format
     * 
     * @param $table Table Name
     * @param $column Column Name
     * @param $cdetails Column Details
     * @param $auto Automation code
     * @return Boolean Always Returns True
     */
    private function makeUserstamp($table,$column,$cdetails,$auto) {
        
        # >>>>>>> CASE MSSQL
        if($auto=='user_ins' || $auto=='user_ui') {            
            $trigger="SET @new_$column = SYSTEM_USER;\n";
            $this->triggerFragment($table,'i','calcs',$trigger);
        }
        if($auto=='user_upd' || $auto=='user_ui') {            
            $trigger="SET @new_$column = SYSTEM_USER;\n";
            $this->triggerFragment($table,'u','calcs',$trigger);
        }
            
        # >>>>>>> CASE MYSQL
        if($auto=='user_ins' || $auto=='user_ui') {            
            $trigger="SET new.$column = Current_user();\n";
            $this->triggerFragment($table,'i','calcs',$trigger);
        }
        if($auto=='user_upd' || $auto=='user_ui') {            
            $trigger="SET new.$column = Current_user();\n";
            $this->triggerFragment($table,'u','calcs',$trigger);
        }
        
        # >>>>>>> CASE POSTGRES
        if($auto=='user_ins' || $auto=='user_ui') {            
            $trigger="new.$column = session_user;\n";
            $this->triggerFragment($table,'i','calcs',$trigger);
        }
        if($auto=='user_upd' || $auto=='user_ui') {            
            $trigger="new.$column = session_user;\n";
            $this->triggerFragment($table,'u','calcs',$trigger);
        }
        
        return true;
    }
    
    /**
     * Codes up a DOMINANT automation for a column.
     * 
     * @param $table The Table
     * @param $column The Column
     * @param $cdetails Column details
     * @return Boolean Always returns true
     */
    private function makeDominant($table,$column,$cdetails) {
        # KFD 12/31/10  We are not doing dominant yet
        return true;
        $xfk      = $this->arr($cdetails['properties'],'xautoform');
        $fullname = $cdetails['properties']['fullname'];

        # We actually take care of MySQL first, because
        # MySQL cannot do a DOMINANT
        $this->platformProblems['mysql'][] =
            "Column '$fullname' has automation DOMINANT, which cannot "
            ."be supported on MySQL due to severe limitations in "
            ."MySQL trigger abilities. "
            .$this->defineHistory($fullname);

        # Set the values to replace based on string or number
        if(in_array($xtype,$this->strings)) {
            $x1 = "'N'";
            $x2 = "='Y'";
        }
        else {
            $x1 = '0';
            $x2 = "<>0";
        }

        # For the rest of the platforms, it is a very simple
        # update statement, so we will just code it once for
        # MS SQL Server and then do string substitutions for
        # the others and keep re-using it.
        $trigger = "UPDATE $table SET $column = $x1 WHERE $column $x2";
        if($xfk<>'') {
            # MOVE_VALID, moved this into vsHCColumns()
            /*
            if(!isset($this->tables2[$table]['foreign_keys'][$xfk])) {
                $this->error(
                    "Column '$fullname' has automation DOMINANT "
                    ."filtered on non-existent foreign key: $xfk. "
                    .$this->defineHistory($fullname)
                    ,'FKNF Foreign Key Not Found.'
                );
                return;
                # <<<<<-------- EARLY RETURN
            }
            */

            # REFACTOR
            $fkdetails = $this->tables2[$table]['foreign_keys'][$xfk];
            $sql       = $this->fkSelfJoin($fkdetails,$table,'@new_');
            #$parent= $fk['properties']['parent'];
            #$sql   = $fk['properties']['sql'];
            #$sql = str_replace("$parent.","@new_",$sql);
            #$sql = str_replace("$table.","",$sql);
            $trigger.=" AND ".$sql;
        }

        # Finish off the update by excluding the current row
        # Make an expression for the primary key of this
        # row, so we exclude it from the update statement
        $selfjoin = $this->selfJoin($table,'','@new_');
        # REFACTOR
        #$pklist = $this->tables2[$table]['properties']['primary_key'];
        #$pks = explode(",",$pklist);
        #$pkx = array();
        #foreach($pks as $pk) {
        #    $pkx[] = "$pk = @new_$pk";
        #}
        #$pklist = implode(' AND ',$pkx);
        $trigger.="\n    AND NOT ($selfjoin);\n";
        
        # <<<------- EARLY RETURN
        # Dominants are disabled KFD 5/30/09 while I try to
        # figure out a few things.
        return;

        # Now the insert and update for MSSQL
        $tcomplete = "IF @new_$column $x2 BEGIN\n"
            ."   ".$trigger
            ."END;\n";
        $this->triggerFragment($table,'i','dominant',$tcomplete);
        $this->triggerFragment($table,'u','dominant',$tcomplete);

        # Now some slight string substitutions for Postgres
        $trigger = str_replace("@new_","new.",$trigger);
        $tcomplete = "IF new.$column $x2 THEN\n"
            ."    ".$trigger
            ."END IF;\n";
        $this->triggerFragment($table,'i','dominant',$tcomplete);
        $this->triggerFragment($table,'u','dominant',$tcomplete);

        return true;
    }

    /**
     * Codes up a QUEUEPOS automation for a column.
     * 
     * @param $table The Table
     * @param $column The Column
     * @param $cdetails Column details
     * @return Boolean Always returns true
     */
    private function makeQueuepos($table,$column,$cdetails) {
        $xfk = $this->arr($cdetails['properties'],'xautoform');
        $fullname = $cdetails['properties']['fullname'];

        # Make an expression for the primary key of this
        # row, so we exclude it from the update statement
        $pklist = $this->tables2[$table]['properties']['primary_key'];
        $pks = explode(",",$pklist);
        $pkx = array();
        foreach($pks as $pk) {
            $pkx[] = "$pk = @new_$pk";
        }
        $pklist = implode(' AND ',$pkx);

        # We actually take care of MySQL first, because
        # MySQL cannot do a DOMINANT
        $this->platformProblems['mysql'][] =
            "Column '$fullname' has automation QUEUEPOS, which cannot "
            ."be supported on MySQL due to severe limitations in "
            ."MySQL trigger abilities. "
            .$this->defineHistory($fullname);


        # For the rest of the platforms, it is a very simple
        # update statement, so we will just code it once for
        # MS SQL Server and then do string substitutions for
        # the others and keep re-using it.
        $trigger 
            ="UPDATE $table SET $column = $column + 1"
            ."    WHERE $column = @new_$column";
        if($xfk<>'') {
            # MOVE_VALID, KFD 12/26/10, moved to vsHCColumns
            /*
            if(!isset($this->tables2[$table]['foreign_keys'][$xfk])) {
                $this->error(
                    "Column '$fullname' has automation QUEUPOS "
                    ."filtered on non-existent foreign key: $xfk. "
                    .$this->defineHistory($fullname)
                    ,'FKNF Foreign Key Not Found.'
                );
                return;
                # <<<<<-------- EARLY RETURN
            }
            */

            $fk = $this->tables2[$table]['foreign_keys'][$xfk];
            $parent= $fk['properties']['parent'];
            $sql   = $fk['properties']['sql'];
            $sql = str_replace("$parent.","@new_",$sql);
            $sql = str_replace("$table.","",$sql);
            $trigger.=" AND ".$sql;
        }

        # Finish off the update by excluding the current row
        $trigger.="\n    AND NOT ($pklist);\n";

        # <<<------- EARLY RETURN
        # Dominants are disabled KFD 5/30/09 while I try to
        # figure out a few things.
        return;
        
        
        # The insert is simple, the update only fires on a change
        $tinsert 
            ="IF @new_$column IS NOT NULL\n"
            ."BEGIN\n"
            ."    ".$trigger."\n"
            ."END;\n";
        $this->triggerFragment($table,'i','queuepos',$tinsert);
        $tupdate 
            ="IF @new_$column <> @old_$column\n"
            ."BEGIN\n"
            ."    ".$trigger."\n"
            ."END;\n";
        $this->triggerFragment($table,'u','queuepos',$tupdate);

        # Now some slight string substitutions for Postgres
        $trigger = str_replace("@new_","new.",$trigger);
        $tinsert 
            ="IF new.$column IS NOT NULL THEN\n"
            ."    ".$trigger."\n"
            ."END IF;\n";
        $this->triggerFragment($table,'i','queuepos',$tinsert);
        $tupdate 
            ="IF new.$column <> old.$column THEN\n"
            ."    ".$trigger."\n"
            ."END IF;\n";
        $this->triggerFragment($table,'u','queuepos',$tupdate);
        
        return true;
    }

    /**
     * Gathers some info and passes off to {@link triggerAggregationsPlatform}
     * 
     * @param $table The Table
     * @param $column The Column
     * @param $cdetails Column details
     * @return Boolean Always returns true
     */
    private function triggerAggregations($table,$fkdetails) {
        # Grab the lists of automations
        $autos    = $fkdetails['autos'];
        $sums     = $this->arr($autos,'sum'   ,array());
        $counts   = $this->arr($autos,'count' ,array());
        $mins     = $this->arr($autos,'min'   ,array());
        $maxes    = $this->arr($autos,'max'   ,array());
        $parent   = $fkdetails['properties']['parent'];
        $fkchd    = implode(',',$fkdetails['properties']['colschd']);

        # Count up how many relevant automations there are.
        # If no aggregrations, return early
        $cols = array_merge(
            array_keys($sums),array_keys($counts)
           ,array_keys($mins),array_keys($maxes)
        );
        if(count($cols)==0) return;
        #
        # <<<<<<<-------  EARLY RETURN
        
        $this->triggerAggregationsPlatform(
            $table,$fkdetails,$sums,$counts,$mins,$maxes,$parent,$fkchd        
        );
    }       
    
    /**
     * Placeholder for platform-specific overrides
     *
     */
    protected function triggerAggregationsPlatform(
        $table,$fkdetails,$sums,$counts,$mins,$maxes,$parent,$fkchd        
    ) {
        return true;    
    } 
        
    /**
     * Contains a bunch of code we want to save for now
     *
     */
    private function makeAggregationsCodeSave() {
        # Put a trap into parent tables to disallow direct
        # writes to aggregate columns unless we are owner
        # (which works because triggers fire as owner)
        /*
        $owners = array(
            'mssql'=>'dbo'
           ,'mysql'=>'system'
           ,'postgres'=>'postgres'
        );
        $inscheck = array();
        $updcheck = array();
        foreach($cols as $col) {
            $inscheck[] = "@new_$col is not null";
            $updcheck[] = "@new_$col <> @old_$col";
        }
        $inssql = implode(" OR ",$inscheck);
        $updsql = implode(" OR ",$updcheck);
        foreach($owners as $platform=>$dbo) {
            $trigger
                ="{IF} $inssql {THEN}\n"
                ."    {IF} triUser() <> '$dbo' {THEN}\n"
                ."        {ERROR:20100:$parent}\n"
                ."    {ENDIF}\n"
                ."{ENDIF}";
            $this->triggerTranslate($parent,'i','updcons',$trigger);
            $trigger
                ="{IF} $updsql {THEN}\n"
                ."    {IF} triUser() <> '$dbo' {THEN}\n"
                ."        {ERROR:20200:$parent}\n"
                ."    {ENDIF}\n"
                ."{ENDIF}";
            $this->triggerTranslate($parent,'u','updcons',$trigger);
        }
        */
        
        
        # This will be the list of changed values.  Every
        # column for every automation is included in this list.
        $a_old_new_vals_match = array();
        
        # These are the three lists of assignments for
        # insert, update, and delete for row-level triggers.
        $xi = array();
        $xu = array();
        $xd = array();

        # These are assignments for statement level triggers
        $mi = array();  # (m)icrosoft (i)nsert
        $md = array();
        $mu = array();
        $mii = array(); # (m)icrosoft (i)nsert (i)nner
        $mdi = array();
        $mui = array();
        
        # The next automation is counts.  Notice we slip in 
        # a line for *ALL* platforms in the header table that
        # initializes these to zero.
        foreach($counts as $colchd=>$colpar) {
            # For count we do not track if the column changed
            #$a_old_new_vals_match[] = "@new_$colchd = @old_$colchd";

            $usl="{SET}@new_$colpar = 0;\n";
            $this->triggerTranslate($parent,'i','calcs',$usl);
            
            # Here are the row-level assignments
            $xi[] = "$colpar = $colpar + 1";
            $xd[] = "$colpar = $colpar - 1";

            # These are the statement level assignments
            $mi[] = "$colpar = $parent.$colpar + new.thecount";
            $md[] = "$colpar = $parent.$colpar - new.thecount";
            $mii[]= "COUNT(*) as thecount";
            $mdi[]= "COUNT(*) as thecount";
        }
        
        # The next automation is SUMS.  Like the counts above,
        # these also get the initial values of zero.
        foreach($sums as $colchd=>$colpar) {
            $a_old_new_vals_match[] = "@new_$colchd = @old_$colchd";

            $usl="{SET}@new_$colpar = 0;\n";
            $this->triggerTranslate($parent,'i','calcs',$usl);
            
            # Row-level assignments
            $xi[] = "$colpar = $colpar + @new_$colchd";
            $xu[] = "$colpar = $colpar + @new_$colchd - @old_$colchd";
            $xd[] = "$colpar = $colpar - @old_$colchd";

            # Statement level assignments
            $mi[] = $mu[] = $md[] = "$colpar = $parent.$colpar + new.$colchd";
            $mii[]= "SUM(inserted.$colchd) as $colchd";
            $mui[]= "SUM(inserted.$colchd - deleted.$colchd) as $colchd";
            $mdi[]= "SUM(0 - deleted.$colchd) as $colchd";
        }

        # Here is MIN.  It is a royal pain.  In plain english this
        # is what we are doing:
        #
        #   ON an insert:
        #      -> if the new value is below a current min,
        #         update the parent, else do nothing
        #   ON a delete:
        #      -> if the deleted value was the min, recalc min
        #   ON an update:
        #      -> if new value is below current min, it is the new min
        #      -> else if old value was the min, recalc min
        #      -> else nothing
        #
        #   ...and of course we have the row-level and 
        #      statement-level versions of both sets of operations.
        #
        # NOTICE that MINS (and maxes) are NOT initialized to
        # zero.  They are NULL when there are no child rows.
        #
        $sqli = $this->fkSelfJoin($fkdetails,'','new.');
        $sqld = $this->fkSelfJoin($fkdetails,'','old.');
        foreach($mins as $colchd=>$colpar) {
            $a_old_new_vals_match[] = "@new_$colchd = @old_$colchd";

            $sqlmini="Select min($colchd) from $table where $sqli";
            $sqlmind="Select min($colchd) from $table where $sqld";
            $xi[]
                ="$colpar = CASE WHEN @new_$colchd < $parent.$colpar\n"
                ."                 OR $colpar IS NULL\n"
                ."               THEN @new_$colchd\n" 
                ."               ELSE $colpar END";
            $xd[]
                ="$colpar = CASE WHEN @old_$colchd > $colpar\n"
                ."               THEN $colpar\n"
                ."               ELSE ($sqlmind) END";
            $xu[]
                ="$colpar = CASE WHEN @new_$colchd < $colpar\n"
                ."               THEN @new_$colchd\n"
                ."               WHEN @old_$colchd > $colpar\n"
                ."               THEN $colpar\n"
                ."               ELSE ($sqlmini) END";
            
            $mi[]
                ="$colpar = CASE WHEN new.new_$colpar < $colpar\n"
                ."                 OR $colpar IS NULL\n"
                ."               THEN new.new_$colpar\n"
                ."               ELSE $colpar END";
            $mu[]
                ="$colpar = CASE WHEN new.new_$colpar < $colpar\n"
                ."               THEN new.new_$colpar\n"
                ."               WHEN new.old_$colpar > $colpar\n"
                ."               THEN $colpar\n"
                ."               ELSE ($sqlmini) END";
            $md[]
                ="$colpar = CASE WHEN new.old_$colpar > $colpar\n"
                ."               THEN $colpar\n"
                ."               ELSE ($sqlmini) END";
            $ins   = "MIN(inserted.$colchd) as new_$colpar";
            $del   = "MIN(deleted.$colchd) as old_$colpar";
            $mii[] = $ins;
            $mdi[] = $del;
            $mui[] = $ins;
            $mui[] = $del;
        }

        # Here is MAX.  It is the mirror image royal pain of MIN
        #
        $sqli = $this->fkSelfJoin($fkdetails,'','new.');
        $sqld = $this->fkSelfJoin($fkdetails,'','old.');
        foreach($maxes as $colchd=>$colpar) {
            $a_old_new_vals_match[] = "@new_$colchd = @old_$colchd";

            $sqlmaxi="Select max($colchd) from $table where $sqli";
            $sqlmaxd="Select max($colchd) from $table where $sqld";
            $xi[]
                ="$colpar = CASE WHEN @new_$colchd > $parent.$colpar\n"
                ."                 OR $colpar IS NULL\n"
                ."               THEN @new_$colchd\n" 
                ."               ELSE $colpar END";
            $xd[]
                ="$colpar = CASE WHEN @old_$colchd < $colpar\n"
                ."               THEN $colpar\n"
                ."               ELSE ($sqlmaxd) END";
            $xu[]
                ="$colpar = CASE WHEN @new_$colchd > $colpar\n"
                ."               THEN @new_$colchd\n"
                ."               WHEN @old_$colchd < $colpar\n"
                ."               THEN $colpar\n"
                ."               ELSE ($sqlmaxi) END";
            
            $mi[]
                ="$colpar = CASE WHEN new.new_$colpar > $colpar\n"
                ."                 OR $colpar IS NULL\n"
                ."               THEN new.new_$colpar\n"
                ."               ELSE $colpar END";
            $mu[]
                ="$colpar = CASE WHEN new.new_$colpar > $colpar\n"
                ."               THEN new.new_$colpar\n"
                ."               WHEN new.old_$colpar < $colpar\n"
                ."               THEN $colpar\n"
                ."               ELSE ($sqlmaxi) END";
            $md[]
                ="$colpar = CASE WHEN new.old_$colpar < $colpar\n"
                ."               THEN $colpar\n"
                ."               ELSE ($sqlmaxi) END";
            $ins   = "MAX(inserted.$colchd) as new_$colpar";
            $del   = "MAX(deleted.$colchd) as old_$colpar";
            $mii[] = $ins;
            $mdi[] = $del;
            $mui[] = $ins;
            $mui[] = $del;
        }

        
        # Collapse the three lists of assignments to the
        # comma-delimited lists
        $ilist = implode(",",$xi);
        $ulist = implode(",",$xu);
        $dlist = implode(",",$xd);
        
        # And the six lists for statement level stuff
        $ilistm= implode(",",$mi);
        $ulistm= implode(",",$mu);
        $dlistm= implode(",",$md);
        $ilisti= implode(",",$mii);
        $ulisti= implode(",",$mui);
        $dlisti= implode(",",$mdi);
        

        # We have completed the list of values for all
        # automations, collapse down the list used to test
        # for changed values.
        $old_new_vals_match = implode(" AND ",$a_old_new_vals_match);
        
        # Generate foreign key matching expressions to new and 
        # old values.
        $njoin = $this->joinParent($fkdetails,'new.',"$parent.");
        $djoin = $this->joinParent($fkdetails,'old.',"$parent.");
        
        # End result on the row-by-row platforms are three
        # expressions: the insert, the delete and update.
        $ins="UPDATE $parent\n"
            ."   SET $ilist\n"
            ." WHERE $njoin;\n";
        $del="UPDATE $parent\n"
            ."   SET $dlist\n"
            ." WHERE $djoin;\n";
        $upd="UPDATE $parent\n"
            ."   SET $ulist\n"
            ." WHERE $njoin;\n";
        
        # Now that we have the expressions, we can do the
        # two easy triggers, which are insert and delete
        if(count($xi)>0) {
            $this->triggerTranslate($table,'i','aggpush',$ins,'!mssql');
        }
        if(count($xd)>0) {
            $this->triggerTranslate($table,'d','aggpush',$del,'!mssql');
        }
        
        # The more complicated case is update, where we check
        # for matching fks and changed values before doing anything
        # Here is what is going on:
        #
        #   The first part of $trg is a conditional that sees if
        #   the foreign key value changed.  If it did, it is kind
        #   of simple, it does a delete from the old value of the
        #   fk, and an insert to the new value.
        #
        #   If there are no update expressions (which happens if
        #   the user only has a COUNT), we stick on an endif.  But
        #   if there are update expressions, we stick on another
        #   conditional that only updates the parents if one of the
        #   child values has actually changed.  If nothing has 
        #   changed there is no reason to update the parent.
        $old_new_fk_match = $this->fkSelfJoin($fkdetails,'@new_','@old_');
        $trg="{IF} NOT($old_new_fk_match) {THEN}\n"
            .$this->indent($ins,4)."\n"
            .$this->indent($del,4)."\n";
        if(count($xu)==0) {
            $trg.="{ENDIF}\n";
        }
        else {
            $trg.="{ELSE}\n"
                ."    {IF} NOT ($old_new_vals_match) {THEN}\n"
                .$this->indent($upd,8)."\n"
                ."    {ENDIF}\n"
                ."{ENDIF}\n";
        }
        $this->triggerTranslate($table,'u','aggpush',$trg,'!mssql');
        
        # STATEMENT LEVEL STUFF.
        # Now we have to do this again for statement-level
        # triggers, which happens to only be ms sql server.
        $ins="-- Normal Insert Push\n"
            ."UPDATE $parent\n"
            ."   SET $ilistm\n" 
            ."  FROM (SELECT $fkchd\n"
            ."              ,$ilisti\n"
            ."          FROM inserted\n"
            ."         GROUP BY $fkchd\n"
            ."        ) new\n"
            ." WHERE $njoin;\n";
        if(count($xi)>0) {
            $this->triggerFragment($table,'i','aggpush',$ins);
        }
        $del="-- Normal delete push\n"
            ."UPDATE $parent\n"
            ."   SET $dlistm\n" 
            ."  FROM (SELECT $fkchd\n"
            ."              ,$dlisti\n"
            ."          FROM deleted\n"
            ."         GROUP BY $fkchd\n"
            ."        ) new\n"
            ." WHERE $njoin;\n";
        if(count($xd)>0) {
            $this->triggerFragment($table,'d','aggpush',$del);
        }

        # Once again, update is complicated, but not all that 
        # complicated.  We are doing three separate statements,
        # one for no fk change, and two for fk change (ins and del).

        # First is normal update, which occurs only if there are
        # update actions to perform.
        $old_new_fk_match=$this->fkSelfJoin($fkdetails,'inserted.','deleted.');
        $old_new_pk_match=$this->SelfJoin($table,'inserted.','deleted.');
        $old_new_vals_match = str_replace(
            '@new_','inserted.',$old_new_vals_match
        );
        $old_new_vals_match = str_replace(
            '@old_','deleted.',$old_new_vals_match
        );
        $fkchdu = 'inserted.'
            .implode("\n,inserted.",$fkdetails['properties']['colschd']);
        if($old_new_vals_match<>'') {
            $upd="-- Normal update push\n"
                ."UPDATE $parent\n"
                ."   SET $ulistm\n" 
                ."  FROM (SELECT $fkchdu\n"
                ."              ,$ulisti\n"
                ."          FROM inserted JOIN deleted\n"
                ."            ON $old_new_fk_match\n"
                ."           AND $old_new_pk_match\n"
                ."         WHERE NOT ($old_new_vals_match)\n"
                ."         GROUP BY $fkchdu\n"
                ."        ) new\n"
                ." WHERE $njoin;\n";
            $this->triggerFragment($table,'u','aggpush',$upd);
        }

        $trg="-- Insert Push when fk changes\n"
            ."UPDATE $parent\n"
            ."   SET $ilistm\n" 
            ."  FROM (SELECT $fkchd\n"
            ."              ,$ilisti\n"
            ."          FROM inserted\n"
            ."         WHERE NOT EXISTS (\n"
            ."            Select * from DELETED\n"
            ."             where $old_new_fk_match\n"
            ."               AND $old_new_pk_match)\n"
            ."         GROUP BY $fkchd\n"
            ."        ) new\n"
            ." WHERE $njoin;\n";
        $this->triggerFragment($table,'u','aggpush',$trg);
        $trg="-- Delete Push when fk changes\n"
            ."UPDATE $parent\n"
            ."   SET $dlistm\n" 
            ."  FROM (SELECT $fkchd\n"
            ."              ,$dlisti\n"
            ."          FROM deleted\n"
            ."         WHERE NOT EXISTS (\n"
            ."            Select * FROM inserted\n"
            ."             where $old_new_fk_match\n"
            ."               AND $old_new_pk_match)\n"
            ."         GROUP BY $fkchd\n"
            ."        ) new\n"
            ." WHERE $njoin;\n";
        $this->triggerFragment($table,'u','aggpush',$trg);
        
        return;
    }

    protected function triggerFetchesPlatform(
        $table,$fkdetails,$fetches,$parent,$fkchd,$comment
    ) {
        return true;
    }
    
    private function triggerFetches($table,$fkdetails) {
        # Grab the lists of automations
        $autos    = $this->arr($fkdetails,'autos',array());
        $fetches  = $this->arr($autos,'fetch' ,array());
        $parent   = $fkdetails['properties']['parent'];
        $fkchd    = implode(',',$fkdetails['properties']['colschd']);
        $comment  = "\n-- FETCHES on fk "
            .$fkdetails['properties']['fullname']
            ." from $parent\n";

        # Count up how many relevant automations there are.
        # If no aggregrations, return early
        if(count($fetches)==0) return true;
        #
        # <<<<<<<-------  EARLY RETURN
        $this->triggerFetchesPlatform(
            $table,$fkdetails,$fetches,$parent,$fkchd,$comment
        );
        
        return;
        #
        # <<<<<<<-------  EARLY RETURN
        #                 All code below is "dead", but being kept
        #                 around to move into platform-specific
        #                 child classes
        

        # Make the list of columns and collapse them down
        $akidcols = array();
        $amssql = array();
        $mysqldec = $mysqlset = $mysqlinto = array();
        foreach($fetches as $parcol=>$kidcol) {
            $mysqldec[]="DECLARE fetch_$kidcol "
                .$this->columnProp($table,$kidcol,'type').";";
            $mysqlset[]= "    {MYSQL}SET new.$kidcol = fetch_$kidcol;";
            $amssql[] = "@new_$kidcol = $parcol";
            $akidcols[] = "@new_$kidcol";
            $mysqlinto[] = "fetch_$kidcol";
        }
        $mssql     = implode(",",$amssql);
        $kidcols   = implode(",",$akidcols);
        $parcols   = implode(",",array_keys($fetches));
        $mysqldec  = implode("\n",$mysqldec);
        $mysqlset  = implode("\n",$mysqlset);
        $mysqlinto = implode(",", $mysqlinto);
        
        # Make the list of not-null or changed fk values
        $afk_changed = array();
        $afk_notnull = array();
        foreach($fkdetails['properties']['colschd'] as $col) {
            $afk_changed[] = "@new_$col <> @old_$col";
            $afk_notnull[] = "@new_$col IS NOT NULL";
        }
        $fk_changed = implode(",",$afk_changed);
        $fk_notnull = implode(",",$afk_notnull);
        
        # Finally the fk join
        $fkjoin = $this->joinParent($fkdetails,'@new_',"$parent.");
        
        # CASE MSSQL
        $trigger="\n"
            ."{IF} $fk_notnull {THEN}\n"
            ."    SELECT $mssql\n"
            ."      FROM $parent\n"
            ."     WHERE $fkjoin\n"
            ."{ENDIF}\n";
        $this->triggerTranslate($table,'i','calcs',$trigger,'mssql');
        $trigger
            ="{IF} $fk_notnull {THEN}\n"
            ."    SELECT $mssql\n"
            ."      FROM $parent\n"
            ."     WHERE $fkjoin\n"
            ."{ENDIF}\n";
        $this->triggerTranslate($table,'u','calcs',$trigger,'mssql');

        # CASE MYSQL
        $triggerbase=
            "    SELECT $parcols\n"
            ."      INTO $mysqlinto\n"
            ."      FROM $parent\n"
            ."     WHERE $fkjoin;\n"
            ."$mysqlset\n"
            ."{ENDIF}\n";
        $trigger=$comment
            .$mysqldec."\n"
            ."{IF} $fk_notnull {THEN}\n"
            .$triggerbase;
        $this->triggerTranslate($table,'i','calcs',$trigger,'mysql');
        $trigger=$comment
            .$mysqldec."\n"
            ."{IF} $fk_changed {THEN}\n"
            .$triggerbase;
        $this->triggerTranslate($table,'u','calcs',$trigger,'mysql');
        
        # case postgres
        $trigger
            ="{IF} $fk_notnull {THEN}\n"
            ."    SELECT INTO $kidcols\n"
            ."                $parcols\n"
            ."      FROM $parent\n"
            ."     WHERE $fkjoin;\n"
            ."{ENDIF}\n";
        $this->triggerTranslate($table,'i','calcs',$trigger,'postgres');
        $trigger
            ="{IF} $fk_changed {THEN}\n"
            ."    SELECT INTO $kidcols\n"
            ."                $parcols\n"
            ."      FROM $parent\n"
            ."     WHERE $fkjoin;\n"
            ."{ENDIF}\n";
        $this->triggerTranslate($table,'u','calcs',$trigger,'postgres');
        
        return true;   
    }
    
    /**
     * Generates code for an upsave.
     *
     * This is a LONG function, but it is very straightforward.
     * It is kept as one function because a lot of variables are
     * generated at the top that are used throughout, and breaking
     * it up would mean passing LOTS of parameters around.
     *
     * The bulk of this routine is validation.
     * 
     * @param $table Source Table
     * @param $upsave Upsave name
     * @param $udetails Upsave details
     * @return Boolean Always Returns true
     */
    function makeUpsave($table,$upsave,$udetails) {
        # - - - - - - - - - - - - - - - - - - - - - - - - 
        #
        # Step 1: Pop off the control properties
        #         All remaining props are column assignments
        #
        # - - - - - - - - - - - - - - - - - - - - - - - - 
        $props = $udetails['properties'];
        $fk      = $this->arr($props,'foreign_key','');
        $flagcol = $this->arr($props,'flag','');
        $flagrst = $this->arr($props,'flag_reset','');
        $aftins  = $this->arr($props,'after_insert','');
        $aftupd  = $this->arr($props,'after_update','');
        $fullname= $this->arr($props,'fullname','');
        
        #  STEP 2, validations, were moved to vsHCUpsave()

        # - - - - - - - - - - - - - - - - - - - - - - - - 
        #
        # Step 3: Build reformatted list of assignments
        #         and validate all columns
        #
        #   MOVE_VALID  KFD 12/26/10, all validations
        #               rem'd out and moved to vsHCUpsave
        # - - - - - - - - - - - - - - - - - - - - - - - - 
        $ttab = $this->tables2[$table]
            ['foreign_keys'][$fk]['properties']['parent'];
        $tcols = $this->tables2[$ttab]['columns'];
        # The list of columns will be properties of the nested 
        # upsaves child element.  To see how this works, look
        # at debug/tables2.txt to see the array structure of
        # a table that has an upsave.
        $columns = $udetails['upsave_columnss']['upsaves']['properties'];
        $fullname = $columns['fullname'];
        # Make this go away, or we'll think its a column
        unset($columns['fullname']);
        /*
        foreach($columns as $destcol=>$srcexpr) {
            # Check the destination column, capture its type
            if(isset($tcols[$destcol])) {
                $destype = $this->columnProp($ttab,$destcol,'type');
                #$destype = $this->tables2[$ttab]['columns'][$destcol]
                #    ['properties']['type'];
            }
            else {
                $this->error(
                    "Upsave '$fullname' names undefined column '$destcol' "
                    ."in parent table '$ttab'.  "
                    .$this->defineHistory($fullname)
                    ,'CNTF Column Not Found.'
                );
                continue;
            }
            
            # Now make sure source column exists
            $srccol = '';
            if(substr($srcexpr,0,1)=='!') {
                $srccol = substr($srcexpr,1);
                if(!isset($this->tables2[$table]['columns'][$srccol])) {
                    $this->error(
                        "Upsave '$fullname' pulls undefined column '$srccol' "
                        ."to save to '$destcol'.  "
                        .$this->defineHistory($fullname)
                        ,'CNTF Column Not Found.'
                    );
                    continue;
                }
                else {
                    $srctype = $this->columnProp($table,$srccol,'type');
                    #$srctype = $this->tables2[$table]['columns'][$srccol]
                    #    ['properties']['type'];
                    if($destype <> $srctype) {
                        $this->error(
                            "Upsave '$fullname' type mismatch.  Destination"
                            ." column '$destcol' is of type '$destype', "
                            ." but source column '$srccol' is of type "
                            ." '$srctype'.  Upsave is "
                            .$this->defineHistory($fullname)
                            ,'TPXX Column Type Mismatch.'
                        );
                    }
                    continue;
                }
            }
        }
        */

        # If we have hit any errors, do not bother continuing
        if(!$this->ok()) return;


        # - - - - - - - - - - - - - - - - - - - - - - - -
        #
        # Step 4: Whew!  Time to actually generate
        #                some triggers!
        #
        #
        # This is annoyingly difficult.  We have to generate
        # multiple lists for the various back ends, and then
        # generate multiple conditionals for the back-ends.
        # This means the code, while very simple, is a lot
        # longer and looks much more complicated than it
        # really is.
        # - - - - - - - - - - - - - - - - - - - - - - - -
        $sets = array();
        foreach($columns as $destcol=>$srcexpr) {
            if(substr($srcexpr,0,1)!='!') {
                $xtype = $this->columnProp($ttab,$destcol,'xtype');
                $retval= $this->sqlFormat($xtype,$srcexpr);
                $sets['mssql'][]    = "$ttab.$destcol = $retval";
                $sets['mysql'][]    = "$ttab.$destcol = $retval";
                $sets['postgres'][] = "$destcol = $retval";
            }
            else {
                $srccol = substr($srcexpr,1);
                $sets['mssql'][]    = "$ttab.$destcol = @new_$srccol";
                $sets['mysql'][]    = "$ttab.$destcol = new.$srccol";
                $sets['postgres'][] = "$destcol = new.$srccol";
            }
        }

        # Now generate the conditionals and resets
        $conditionals = array();
        $resets       = array(
            'mssql'=>''
            ,'mysql'=>''
            ,'postgres'=>''
        );
        if(!$flagcol) {
            $conditionals['mssql']    = '1=1';
            $conditionals['mysql']    = '1=1';
            $conditionals['postgres'] = '1=1';
        }
        else {
            $xtype = $this->columnProp($table,$flagcol,'xtype');
            if(in_array($xtype,$this->numerics)) {
                $conditionals['mssql']    = "COALESCE(@new_$flagcol,0)<>0";
                $conditionals['mysql']    = "COALESCE(new.$flagcol,0)<>0";
                $conditionals['postgres'] = "COALESCE(new.$flagcol,0)<>0";
                if($flagrst=='Y') {
                    $resets['mssql']    = "SET @new_$flagcol = 0;\n";
                    $resets['mysql']    = "SET new.$flagcol = 0;\n";
                    $resets['postgres'] = "new.$flagcol = 0;\n";
                }
            }
            else {
                $conditionals['mssql']    = "COALESCE(@new_$flagcol,'')='Y'";
                $conditionals['mysql']    = "COALESCE(new.$flagcol,'')='Y'";
                $conditionals['postgres'] = "COALESCE(new.$flagcol,'')='Y'";
                if($flagrst=='Y') {
                    $resets['mssql']    = "SET @new_$flagcol = 'N';\n";
                    $resets['mysql']    = "SET new.$flagcol = 'N';\n";
                    $resets['postgres'] = "new.$flagcol = 'N';\n";
                }
            }
        }

        # Let's capture the where condition
        $sql = $this->tables2[$table]['foreign_keys'][$fk]
            ['properties']['sql'];

        # Code the triggers, or the start of them
        $where = str_replace("$table.","@new_",$sql);
        $trigger="
        IF {$conditionals['mssql']} 
        BEGIN
            UPDATE $ttab SET ".implode("\n            ,",$sets['mssql'])."        
             WHERE $where;
            {$resets['mssql']}
        END\n";
        if($aftins=='Y') {
            $this->triggerFragment($table,'i','upsave',$trigger);
        }
        if($aftupd=='Y') {
            $this->triggerFragment($table,'u','upsave',$trigger);
        }
        
        $where = str_replace("$table.","new.",$sql);
        $trigger="
        IF {$conditionals['mysql']} THEN 
            UPDATE $ttab SET ".implode("\n            ,",$sets['mysql'])."        
             WHERE $where;
            {$resets['mysql']}
        END IF;\n";
        if($aftins=='Y') {
            $this->triggerFragment($table,'i','upsave',$trigger);
        }
        if($aftupd=='Y') {
            $this->triggerFragment($table,'u','upsave',$trigger);
        }
            
        $where = str_replace("$table.","new.",$sql);
        $trigger="
        IF {$conditionals['postgres']} THEN 
            UPDATE $ttab SET ".implode("\n            ,",$sets['postgres'])."        
             WHERE $where;
            {$resets['postgres']}
        END IF;\n";
        if($aftins=='Y') {
            $this->triggerFragment($table,'i','upsave',$trigger);
        }
        if($aftupd=='Y') {
            $this->triggerFragment($table,'u','upsave',$trigger);
        }
        
        return true;
    }
    
    /**
     * Generate code for histories.
     * 
     * Like the routine {@link makeUpsaves}, this routine is quite
     * long, but it is simple and linear.  All validations are done
     * first, then lists are compiled, and finally the trigger
     * fragments are generated.
     * 
     * @param $table
     * @param $history
     * @param $hdetails
     * @return unknown_type
     */
    private function makeHistory($table,$history,$hdetails) {
        # MOVE_VALID  KFD 12/26/10  All of this validation code
        #             was moved up to vsHCHistory(), but because
        #             it seems to be generating values used later,
        #             it is still here.
    
        # Get two properties we will use frequently
        $fullname = $hdetails['properties']['fullname'];
        $htable = $this->arr($hdetails['properties'],'history','');
        
        # First validation, make sure target table exists
        if(!isset($this->tables2[$htable])) {
            $this->error(
                "History '$fullname' target table not found: '$htable'. "
                .$this->defineHistory($fullname)
                ,'TNTF Table Not Found.'
            );
            return;
        }
        $hfn = $this->tables2[$htable]['properties']['fullname'];

        
        # Next validations are to make sure save_new and save_old
        # actually hit columns of same type, and that source and
        # destination exist.  But before we do that, we need to
        # get the list of saves in usable forms.
        $hnews = array();
        $holds = array();
        $hdeltas=array();
        if(isset($hdetails['hnews'])) {
            unset($hdetails['hnews']['save_new']['properties']['fullname']);
            $hnews = $hdetails['hnews']['save_new']['properties'];
        } 
        if(isset($hdetails['holds'])) {
            unset($hdetails['holds']['save_old']['properties']['fullname']);
            $holds = $hdetails['holds']['save_old']['properties'];
        } 
        if(isset($hdetails['hdeltas'])) {
            $dfullname = $hdetails['hdeltas']['deltas']['properties']['fullname'];
            unset($hdetails['hdeltas']['deltas']['properties']['fullname']);
            $hdeltas = $hdetails['hdeltas']['deltas']['properties'];
        } 
        
        
        if(count($hnews)+count($holds)+count($hdeltas)==0) {
            $this->error(
                "History '$fullname' has nothing to do!  Define at least "
                ."one 'save_new', 'save_old' or 'deltas'. "
                .$this->defineHistory($fullname)
                ,'HNON History Has Nothing To Do.'
            );
            return;
        }
        
        
        # Now lets combine and validate all three lists
        $hall = array_merge($hnews,$holds,$hdeltas);
        foreach($hall as $destcol=>$srcexpr) {
            if(!isset($this->tables2[$htable]['columns'][$destcol])) {
                $this->error(
                    "History '$fullname' saves to undefined column "
                    ."'$destcol' in target table '$htable'.  "
                    ."$fullname ".$this->defineHistory($fullname)
                    ." $htable ".$this->defineHistory($fullname)
                    ,'T_CNTF Column Not Found.'                
                );
            }
            if(substr($srcexpr,0,1)=='!') {
                $srccol = substr($srcexpr,1); 
                if(!isset($this->tables2[$table]['columns'][$srccol])) {
                    $this->error(
                        "History '$fullname' pulls undefined column "
                        ."'$srccol' to save to '$destcol'. "
                        .$this->defineHistory($fullname)
                        ,'T_CNTF Column Not Found.'                
                        );
                }
                else {
                    $dtype = $this->columnProp($htable,$destcol,'type');
                    $stype = $this->columnProp($table ,$srccol ,'type');
                    $dfn   = $this->columnProp($htable,$destcol,'fullname');
                    $sfn   = $this->columnProp($table ,$srccol ,'fullname');
                    if($dtype<>$stype) {
                        $this->error(
                            "History '$fullname' type mismatch.  Destination"
                            ." column '$destcol' is of type '$dtype', "
                            ." but source column '$srccol' is of type "
                            ." '$stype'.  Upsave is "
                            .$this->defineHistory($fullname)
                            ." Destination column is "
                            .$this->defineHistory($dfn)
                            ." Source column is "
                            .$this->defineHistory($sfn)
                            ,'TPXX Column Type Mismatch.'
                        );           
                    }
                }
            }
        }
        
        # We have a separate validation for deltas, they must all
        # be column expressions and numerical.
        foreach($hdeltas as $destcol=>$srcexpr) {
            if(substr($srcexpr,0,1)!='!') {
                $this->error(
                    "History '$fullname' delta expression '$srcexpr' "
                    ."must name a column, literals are not supported. "
                    ." Deltas ".$this->defineHistory($dfullname)
                    ,'HDCL History Delta Must Be a Column.'
                );
                continue;
            }
            $srccol = substr($srcexpr,1);
            $dtype = $this->columnProp($table,$srccol,'xtype');
            if(!in_array($dtype,$this->numerics)) {
                $srccol = substr($srcexpr,1);
                $this->error(
                    "History '$fullname' delta column '$srccol' "
                    ."must be a number type."
                    ." Deltas ".$this->defineHistory($dfullname)
                    ,'TPHD Bad Type For History Delta.'
                );
                continue;
            }
        }

        # See if there is a char column "action" in 
        # destination table
        $action = false;
        if(isset($this->tables2[$htable]['columns']['action'])) {
            $xtype=$this->columnProp($htable,'action','xtype');
            $xprec=$this->columnProp($htable,'action','xprecision');
            $xfn  =$this->columnProp($htable,'action','fullname');
            if(!in_array($xtype,array('char','varchar'))) {
                $this->error(
                    "History '$fullname' destination table '$htable'"
                    ." has an 'action' column which must be type"
                    ." 'char(7)' or 'varchar(7)'.  Action column "
                    .$this->defineHistory($xfn)
                    ,'HACT Bad Type For History Action Column.'
                );
            }
            else {
                if($xprec < 7) {
                    $this->error(
                        "History '$fullname' destination table '$htable'"
                        ." has an 'action' column which is too small,"
                        ." it should be 'char(7)' or 'varchar(7)."
                        ."  Action column ".$this->defineHistory($xfn)
                        ,'HACS History Action Column Too Small.'
                    );
                }
                else {
                    $action = true;
                }
            }
        } 
        
        
        # Putting the error check here means that all histories
        # will be examined for errors, but no code will be 
        # generated if there actually are some errors.
        if(!$this->ok()) return;
        
        # This is the list of columns for platform and action.
        # we preload them empty so downstream code doesn't have
        # to have annoying conditionals to see if they exist.
        $icols = array(
            'mssql'=>array('i'=>array(),'u'=>array(),'d'=>array())        
            ,'mysql'=>array('i'=>array(),'u'=>array(),'d'=>array())        
            ,'postgres'=>array('i'=>array(),'u'=>array(),'d'=>array())        
        );
        
        # If there is an action column, put in those assignments
        if($action) {
            $icols['mssql']['u']['action'] = "'update'";
            $icols['mysql']['u']['action'] = "'update'";
            $icols['postgres']['u']['action'] = "'update'";
            if(count($hnews)>0 || count($hdeltas)>0) {
                $icols['mssql']['i']['action'] = "'insert'";
                $icols['mysql']['i']['action'] = "'insert'";
                $icols['postgres']['i']['action'] = "'insert'";
            }
            if(count($holds)>0 || count($hdeltas)>0) {
                $icols['mssql']['d']['action'] = "'delete'";
                $icols['mysql']['d']['action'] = "'delete'";
                $icols['postgres']['d']['action'] = "'delete'";
            }
        }

        # Take the "news" values and create the assignments
        # for inserts and updates.
        foreach($hnews as $destcol=>$srcexpr) {
            if(substr($srcexpr,0,1)!='!') {
                $xtype = $this->columnProp($htable,$destcol,'xtype');
                $retval= $this->sqlFormat($xtype,$srcexpr);
                $icols['mssql']['i'][$destcol]    = $retval;
                $icols['mysql']['i'][$destcol]    = $retval;
                $icols['postgres']['i'][$destcol] = $retval;
                $icols['mssql']['u'][$destcol]    = $retval;
                $icols['mysql']['u'][$destcol]    = $retval;
                $icols['postgres']['u'][$destcol] = $retval;
            }
            else {
                $srccol = substr($srcexpr,1);
                $icols['mssql']['i'][$destcol]    = "new.$srccol";
                $icols['mysql']['i'][$destcol]    = "new.$srccol";
                $icols['postgres']['i'][$destcol] = "new.$srccol";
                $icols['mssql']['u'][$destcol]    = "new.$srccol";
                $icols['mysql']['u'][$destcol]    = "new.$srccol";
                $icols['postgres']['u'][$destcol] = "new.$srccol";
            }
        }

        # Take the olds and make assignments for updates and deletes
        foreach($holds as $destcol=>$srcexpr) {
            if(substr($srcexpr,0,1)!='!') {
                $xtype = $this->columnProp($htable,$destcol,'xtype');
                $retval= $this->sqlFormat($xtype,$srcexpr);
                $icols['mssql']['d'][$destcol]    = $retval;
                $icols['mysql']['d'][$destcol]    = $retval;
                $icols['postgres']['d'][$destcol] = $retval;
                $icols['mssql']['u'][$destcol]    = $retval;
                $icols['mysql']['u'][$destcol]    = $retval;
                $icols['postgres']['u'][$destcol] = $retval;
            }
            else {
                $srccol = substr($srcexpr,1);
                $icols['mssql']['d'][$destcol]    = "old.$srccol";
                $icols['mysql']['d'][$destcol]    = "old.$srccol";
                $icols['postgres']['d'][$destcol] = "old.$srccol";
                $icols['mssql']['u'][$destcol]    = "old.$srccol";
                $icols['mysql']['u'][$destcol]    = "old.$srccol";
                $icols['postgres']['u'][$destcol] = "old.$srccol";
            }
        }

        # Take the deltas and assign the three actions
        #
        foreach($hdeltas as $destcol=>$srcexpr) {
            $srccol = substr($srcexpr,1);
            $icols['mssql']['i'][$destcol]    = "new.$srccol";
            $icols['mysql']['i'][$destcol]    = "new.$srccol";
            $icols['postgres']['i'][$destcol] = "new.$srccol";
            $icols['mssql']['d'][$destcol]    = "0-old.$srccol";
            $icols['mysql']['d'][$destcol]    = "0-old.$srccol";
            $icols['postgres']['d'][$destcol] = "0-old.$srccol";
            $icols['mssql']['u'][$destcol]    = "@new_$srccol - @old_$srccol";
            $icols['mysql']['u'][$destcol]    = "new.$srccol - old.$srccol";
            $icols['postgres']['u'][$destcol] = "new.$srccol - old.$srccol";
        }

        # We can actually do all platforms in a loop, we don't
        # need any special platform-specific code here.
        foreach($icols as $platform=>$pdetails) {
            foreach($pdetails as $action=>$cols) {
                if(count($cols)>0) {
                    $cols = $icols['mysql'][$action];
                    $colnames = array_keys($cols);
                    if($platform<>'mssql') {
                        $trigger =
                            "insert into $htable\n"
                            ."(".implode("\n  ,",$colnames).")\n"
                            ."VALUES "
                            ."(".implode("\n  ,",$cols).");\n";
                    }
                    else {
                        switch($action) {
                            case 'i':
                                $from = 'inserted new';
                                break;
                            case 'd':
                                $from = 'deleted old';
                                break;
                            case 'u':
                                $pkmatch = $this->selfJoin($table,'new.','old.');
                                $from = 'inserted new join deleted old on '.$pkmatch;
                        }
                        $trigger =
                            "insert into $htable\n"
                            ."(".implode("\n  ,",$colnames).")\n"
                            ."SELECT ".implode("\n  ,",$cols)."\n"
                            ." FROM $from";
                    }
                    $this->triggerFragment(
                        $table,$action,'history',$trigger
                    );
                }
            }
        }

        return true;
    }

    /**
     * Recurses the {@link triggers} array and assembles trigger commands
     *
     * This program recurses the {@link triggers} array and assembles
     * complete create/alter commands and adds them to {@link builds}.
     * 
     * Helper: {@link atOne}
     * 
     * Previous Step: {@link generateTriggers}
     * 
     * Next Step: {@link writeScript}
     *
     * @return Boolean Always returns true
     */
    private function assembleTriggers() {
         foreach($this->triggers as $table=>$tdetails) {
            foreach($tdetails as $uid=>$sections) {
                # The before
                $text = '';
                foreach($this->tbefore as $section) {
                #foreach($sections as $section=>$snippets) {
                    $snippets = $this->arr($sections,$section,array());
                    if(count($snippets)==0) continue;
                    $text.="-- ---------------------------\n";
                    $text.="-- Section $section\n";
                    $text.="-- ---------------------------\n";
                    foreach($snippets as $snippet) {
                        $text.=$snippet;
                    #    $lines = explode("\n",$snippet);
                    #    foreach($lines as $idx=>$line) {
                    #        $text.="    ".$line."\n";
                    #    }
                    }
                }
                if($text<>'') {
                    $this->finishTriggerPlatform(
                        $table,$uid,$text,'before'
                    );
                }

                # The after
                $text = '';
                foreach($this->tafter as $section) {
                #foreach($sections as $section=>$snippets) {
                    $snippets = $this->arr($sections,$section,array());
                    if(count($snippets)==0) continue;
                    $text.="    -- ---------------------------\n";
                    $text.="    -- Section $section\n";
                    $text.="    -- ---------------------------\n";
                    foreach($snippets as $snippet) {
                        $lines = explode("\n",$snippet);
                        foreach($lines as $idx=>$line) {
                            $text.="    ".$line."\n";
                        }
                    }
                }
                if($text<>'') {
                    $this->finishTriggerPlatform(
                        $table,$uid,$text,'after'
                    );
                }
            }
        }
        return true;
    }
    
    /*
    private function atPK($platform,$table,$uid,$spk) {
        $sql = '';
        foreach($spk as $pk) {
            if($pk<>'Y') continue;
            
            # Build the basic select, same for everybody
            $pklist = $this->tables2[$table]['properties']['primary_key'];
            $pkcols = explode(',',$pklist);
            $apksql = array();
            foreach($pkcols as $pkcol) {
                $apksql[] = "$table.$pkcol = new.$pkcol";
            }
            $pksql = implode(" AND ",$apksql);
            
            switch($platform) {
                case 'db2':
                    break;
                case 'mssql':
                    break;
                case 'mysql':
                    if($uid<>'i') continue;
                    
                    $this->builds[$platform]['indexes'][] = 
                        "ALTER TABLE $table ADD PRIMARY KEY "
                        ."($pklist)";
                    break;
                case 'oracle':
                    break;
                case 'postgres':
                    # To prevent duplication, assign normal pks
                    # only on insert
                    if($uid<>'i') continue;
                    
                    $this->builds[$platform]['indexes'][] = 
                        "ALTER TABLE $table ADD PRIMARY KEY "
                        ."($pklist)";
                    #$sql="IF (select count(*) \n"
                    #    ."      FROM $table\n"
                    #    ."     WHERE $pksql ) > 0 THEN\n"
                    #    ."    RAISE EXCEPTION 'HELLO I AM PK ERROR';\n"
                    #    ."END IF;\n";
            }
        }
        
        # Sometimes we don't actually make any trigger text, we go
        # directly to build scripts
        if($sql<>'') {
            $this->addTextToTrigger($sql,'PRIMARY KEY');
        }
    }
    */
    
    /*
    private function atPK($platform,$table,$uid,$spk) {
        foreach($spk as $pk) {
            if($pk<>'Y') continue;
            
            switch($platform) {
                case 'db2':
                case 'mssql':
                case 'mysql':
                case 'oracle':
                case 'postgres':
            }
        }
    }
    
    */
    
    /*
    private function addTextToTrigger($sql,$section) {
        if($sql=='') return;

        $text = '';
        $text.="    -- ---------------------------\n";
        $text.="    -- Section $section\n";
        $text.="    -- ---------------------------\n";
        $lines = explode("\n",$sql);
        foreach($lines as $idx=>$line) {
            $text.="    ".$line."\n";
            #if(substr($line,0,7)!='DECLARE') {
            #}
            #else {
            #    $declares = trim(substr($line,7));
            #}
        }
        $this->trigger.=$text;
    }
    */
    
    protected function finishTriggerPlatform($table,$uid,$tbody,$ba) {
        $uidx = $uid;
        if($uid=='i') $uid = 'insert';
        if($uid=='u') $uid = 'update';
        if($uid=='d') $uid = 'delete';
        
        # Pull out the declares
        $declares 
            = isset($this->declares[$table][$uidx])
            ? implode("\n",$this->declares[$table][$uidx])."\n"
            : '';
        $declares = $this->indent($declares,4);
        

        # Make up the trigger name
        $tf    = "{$table}_{$uid}_$ba";

        # Getting to work, let's do all pretty-fying of
        # trigger code.  Step 1 is to indent comma-lists
        $lines = explode("\n",$tbody);
        $tbody = '';
        foreach($lines as $line) {
            if(strpos($line,",")===false) {
                $tbody.="$line\n";
            }
            else {
                # Split and take care of the first line
                $commas = explode(",",$line);
                $tbody.=$commas[0]."\n";
                
                # Now indent the rest of the lines
                $xspace = strrchr($commas[0],' ');
                $xatvar = strrchr($commas[0],'@'); # mssql select into...
                if($xatvar!==false) {
                    $indent = strpos($commas[0],$xatvar)-1;
                }
                else {
                    $indent = strpos($commas[0],$xspace);
                }
                for($x=1;$x<=count($commas)-1;$x++) {
                    $tbody.=str_repeat(' ',$indent).",".$commas[$x]."\n";
                }
            }
        }
        
        
        # Final pretty step is to indent
        $indent = $this->platform=='mssql' ? 8 : 4;
        $tbody    = $this->indent($tbody,$indent);
        
        /*
        if($platform=='db2') {
            switch($uid) {
                case 'insert':
                    $extra=' REFERENCING NEW AS NEW';
                    break;
                case 'update':
                    $extra=' REFERENCING OLD AS OLD\n REFERENCING NEW AS NEW';
                    break;
                case 'delete':
                    $extra=' REFERENCING OLD AS OLD';
            }
            $tbody
                ="\n\n"
                ."CREATE TRIGGER $tf\n"
                ." BEFORE ".strtoupper($uid)."\n"
                ." ON $table\n"
                ." $extra\n"
                ." FOR EACH ROW\n"
                ."BEGIN ATOMIC\n"
                .$tbody
                ."END\n";
        }
        */

        if($this->platform=='mssql' && $ba =='before') {
            # For MS SQL we need a bunch of lists of columns
            $new = $old = '';
            $cnew= $cold= $cols = $colsset = array();
            foreach($this->tables2[$table]['columns'] as $column=>$cdetails) {
                $col = $column.' '.$cdetails['properties']['type'];
                $new.= '    DECLARE @new_'.$col.";\n";
                $old.= '    DECLARE @old_'.$col.";\n";
                $cols[] = $column;
                $cnew[] = '@new_'.$column;
                $cold[] = '@old_'.$column;
                $colsset[]="$column = @new_$column";
            }
            $new.="\n";
            $old.="\n";
            $cols = implode("\n         ,",$cols);
            $cnew = implode("\n         ,",$cnew);
            $cold = implode(",",$cold);
            $colsset = implode("\n              ,",$colsset);
            $pks  = explode(
            	',',$this->tables2[$table]['properties']['primary_key']
            );
            $pktab = $pkins = array();
            foreach($pks as $pk) {
                $pktab[] = "$table.$pk = @old_$pk";
            }
            $pktab  = implode(' AND ',$pktab);
            $icursor
                ="    DECLARE trig_ins_$table cursor for \n"
                ."            SELECT * FROM inserted;\n"
                ."    OPEN trig_ins_$table;\n\n";
            $iclose
                ="    CLOSE trig_ins_$table;\n"
                ."    DEALLOCATE trig_ins_$table;\n\n";
            $dcursor
                ="    DECLARE trig_del_$table cursor for \n"
                ."            SELECT * FROM deleted;\n"
                ."    OPEN trig_del_$table;\n\n";
            $dclose
                ="    CLOSE trig_del_$table;\n"
                ."    DEALLOCATE trig_del_$table;\n\n";
            $ifetch
                ="    FETCH NEXT FROM trig_ins_$table\n"
                ."     INTO $cnew;\n\n";
            $dfetch
                ="    FETCH NEXT FROM trig_del_$table\n"
                ."     INTO $cold;\n\n";
            switch($uid) {
                case 'insert':
                    $declares=$new;
                    $makecursor = $icursor;
                    $fetch = $ifetch;
                    $closecursor= $iclose;
                    $command
                        ="        INSERT INTO $table (\n"
                        ."            $cols)\n"
                        ."        VALUES (\n"
                        ."            $cnew);\n\n";
                    break;
                case 'update':
                    $declares=$new.$old;
                    $makecursor = $icursor.$dcursor;
                    $fetch = $ifetch.$dfetch;
                    $closecursor= $iclose.$dclose;
                    $command
                        ="        UPDATE $table\n"
                        ."           SET ".$colsset."\n"
                     	."         WHERE $pktab\n\n";
                    break;
                case 'delete':
                    $declares=$old;
                    $makecursor = $dcursor;
                    $fetch   = $dfetch;
                    $closecursor= $dclose;
                    $command = "DELETE FROM $table WHERE $pktab\n";
            }
            $tbody
                ="\n\n"
                ."CREATE TRIGGER $tf\n"
                ."ON $table\n"
                ."INSTEAD OF $uid\n"
                ."AS\n"
                ."BEGIN\n"
                ."SET NOCOUNT ON;\n"
                .$declares
                .$makecursor
                .$fetch
                ."    WHILE @@Fetch_status = 0 "
                ."    BEGIN\n"
                .$tbody."\n"
                ."        -- -------------------------\n"
                ."        -- Update/insert fetch next \n"
                ."        -- -------------------------\n"
                .$command
                .$this->indent($fetch,4)
                ."\n"
                ."    END  -- Cursor iteration\n"
                ."\n"
                .$closecursor
                ."END\n";
        }

        if($this->platform=='mssql' && $ba =='after') {
            $tbody
                ="\n\n"
                ."CREATE TRIGGER $tf\n"
                ."ON $table\n"
                ."AFTER $uid\n"
                ."AS\n"
                ."BEGIN\n"
                ."SET NOCOUNT ON;\n"
                .$tbody
                ."END\n";
        }
        
        # CASE MYSQL
        if($this->platform=='mysql') {
            $tbody
                ="CREATE TRIGGER $tf\n"
                ."$ba ".strtoupper($uid)." ON $table\n"
                ."FOR EACH ROW BEGIN\n"
                .$declares
                .$tbody
                ."END;\n"
                ."\n";
        }
        
        /*
        if($platform=='oracle') {
            $tbody 
                ="\n\n"
                ."-- --------------------------------------------- --\n"
                ."CREATE OR REPLACE TRIGGER {$tf}()\n"
                ." BEFORE $uid on $table\n"
                ." FOR EACH ROW\n"
                ."DECLARE\n"
                ."BEGIN\n"
                .$tbody."\n"
                ."END;\n";
        }
        */
        
        # Now the long-running annoying part, assembling for
        # the various flavors of SQL
        $return = 'new';
        if($uid=='delete') $return = 'old';
        if($this->platform=='postgres') {
            # step 1, assemble the function
            $tbody 
                ="\n\n"
                ."-- --------------------------------------------- --\n"
                ."CREATE OR REPLACE FUNCTION {$tf}_F()\n"
                ."RETURNS TRIGGER\n AS \$BODY\$\n"
                ."DECLARE\n"
                ."BEGIN\n"
                ."    SET SEARCH_PATH TO PUBLIC;\n"
                .$tbody."\n"
                ."    RETURN $return;\n"
                ."END; \$BODY\$\n"
                ."LANGUAGE 'plpgsql' SECURITY DEFINER;\n\n"
                ."ALTER FUNCTION {$tf}_F() OWNER TO postgres;\n\n"
                ."CREATE TRIGGER {$tf}_T\n"
                ."   $ba $uid\n"
                ."   ON $table\n"
                ."   FOR EACH ROW\n"
                ."   EXECUTE PROCEDURE {$tf}_F();\n\n";
        }
        

        # At the very end, add to script
        $this->builds['triggers'][] = $tbody;
        
        return true;
    }
        
    
    
    
    /* ---------------------------------------------------------------- */
    
    
    /**
     * Writes out dictionary in various forms for use by application.
     * 
     * This routine takes the dictionary and writes it out in various
     * forms that will be useful to the application.  The various
     * forms are meant to allow application code to use whichever
     * is more to their liking.
     * 
     * PLACEHOLDER.  Right now just outputs spec2 in a few formats.
     * 
     * Helper: {@link @wdOneArray}
     * Helper: {@link @wdXmlRecurse}
     *
     * Previous Step: {@link writeScript}
     *
     * Next Step: {@link writeDebug}
     * 
     * @return Boolean Always returns true
     */
    private function writeDictionary() {
        $dir = $this->dir;
        $out=$dir.'out/';
        $this->log("Output directory is $out");
        
        if(!is_dir($out)) mkdir($out);
        
        # Get this all brainstormed with spec2 itself  
        $this->wdOneArray($out,$this->spec2,'complete');
        
        # Now output all of the tables
        # And some hard-coded table stuff
        foreach($this->tables2 as $table=>$tdetails) {
            file_put_contents($out."$table.json",$this->json_format( json_encode($tdetails) ));
            
            $yaml = Spyc::yamlDump($tdetails,4);
            file_put_contents($out."$table.yaml",$yaml);
        }
        
     
        # Now output the scripts
        $fout = FOPEN($out."build.{$this->platform}.sql","w");
     
        $cno = 1;
        foreach($this->builds as $section=>$commands) {
            foreach($commands as $idx=>$command) {
                FPUTS($fout,"\n-- COMMAND --\n");
                FPUTS($fout,'-- Command # '.$cno."\n");
                FPUTS($fout,$command);
                $cno++;
            }
        }
        FCLOSE($fout);
        
        
        
        return true;
    }
    
    /**
     * Helper to {@link writeDictionary}
     * 
     * This is a helper program to writeDictionary, it accepts
     * a directory, an array, and a base file name, and outputs
     * the array in a variety of forms.  Hopefully one of the forms
     * will be usable by any particular application language.
     * 
     * @param $dir
     * @param $array
     * @param $filebase
     * @return Boolean Always returns true
     */
    private function wdOneArray($dir,$array,$filebase) {
        # Make an xml string ourselves recursively
        #$string = '<?xml version="1.0"? >'."\n<specification>\n";
        #$string.=$this->wdXMLRecurse($array);
        #$string.= "\n</specification>";
        #file_put_contents($dir.$filebase.'.xml',$string);
        
        # Write out a serialized version
        #file_put_contents(
        #    $dir.$filebase.'.serialized.txt'
        #    ,serialize($array)
        #);
        
        
        # Write out an exported version
        #file_put_contents(
        #    $dir.$filebase.'.php'
        #    ,"\$load=\n".var_export($array,true).";"    
        #);
        
        # Finally, write out a YAML Version
        $yaml = Spyc::yamlDump($array,4);
        file_put_contents($dir.$filebase.'.yaml',$yaml);
     
     
        # create ExtJS stores
        $this->createStoresFile($array, $dir .'app_stores.js' );
        return true;
    }
    
    /**
     * Helper to {@link wdOneArray}
     * 
     * This is a helper program to writeDictionary, it accepts
     * an array, and a base file name, and outputs
     * an JavaScript file with a Ext.data.Store object for each table
     * 
     * @param $array
     * @param $file
     * @return Boolean Always returns true
     */
    private function createStoresFile( $array, $file ) {
        # KFD 12/30/10  If "modules" does not exist there was some
        #               problem and the build did not complete, so
        #               don't do anything here
        if(!isset($array['modules'])) return;
    
        $js = 'Ext.ns(\'App\');' ."\n";
        foreach( $array['modules'] as $module_name=>$module ) {
            foreach( $module['tables'] as  $table_name=>$table ) {
                $js .= 'App.stores.' .$table_name .' = new Ext.data.Store({' ."\n";
                $js .= "\t" .'autoDestroy: false,' ."\n";
                $js .= "\t" .'url: controller.php,' ."\n";
                $js .= "\t" .'baseParams: {' ."\n";
                $js .= "\t\t" .'module: \'' .$module_name .'\',' ."\n";
                $js .= "\t\t" .'table: \'' .$table_name .'\',' ."\n";
                $js .= "\t\t" .'action: \'getRecords\',' ."\n";
                $js .= "\t\t" .'start: 0,' ."\n";
                $js .= "\t\t" .'limit: 50,' ."\n";
                $js .= "\t" .'},' ."\n";
                $js .= "\t" .'reader: new Ext.data.JsonReader(' ."\n";
                $js .= "\t\t" .'{' ."\n";
                $js .= "\t\t\t" .'root: \'records\',' ."\n";
                $js .= "\t\t\t" .'totalProperty: \'total\',' ."\n";
                $js .= "\t\t\t" .'successProperty: \'success\',' ."\n";
                $js .= "\t\t" .'},[' ."\n";
                
                $cols_js = array();
                foreach( $table['columns'] as $column_name=>$col ) {
                    array_push( $cols_js, 
                        "\t\t\t" .'{' 
                            .'name: \'' .$column_name 
                            .'\', type: \'' .$this->getExtDataType( $col['properties']['xtype'] ) 
                            .'\', mapping: \'' .$column_name .'\'' 
                        .'}'
                    );
                }
                
                $js .= join( ',' ."\n", $cols_js ) ."\n";
                $js .= "\t\t" .']' ."\n";
                $js .= "\t" .')' ."\n";
                $js .= '});' ."\n\n";
            }
        }
        
        file_put_contents($file, $js);
        return true;
    }
    
    /**
     * Takes in an xtype and return a matching Ext.data.Types object
     * 
     * @param $type
     * @return String Returns Ext.data.Types String
     */
    
    private function getExtDataType( $type ) {
        if ( $type === 'int' ) {
            return 'int';
        } else if ( $type === 'char' ) {
            return 'string';
        } else if ( $type === 'datetime' ) {
            return 'string';
        } else if ( $type === 'numeric' ) {
            return 'float';
        }
    }
    
    private function wdXMLRecurse($array,$indent='') {
        $string = '';
        foreach($array as $key=>$value) {
            $string.="\n$indent<$key>";
            if(is_array($value)) {
                $string.=$this->wdXMLRecurse($value,$indent.'    ');
                $string.="\n$indent</$key>";
            }
            else {
                $string.=$value."</$key>";
            }
        }
        return $string;
    }

    # ========================================================================
    #
    # Area: 
    #
    # ========================================================================
    /**
     * Gets description of database state before upgrade
     * 
     * PLACEHOLDER.  This is a placeholder routine right now,
     * it makes up empty arrays for anything that downstream
     * code will require.
     * 
     * @return Boolean Always returns true
     */
    private function getCurrent() {
        include 'triGetCurrentPostgres.php';
        $gc = new getCurrentPostgres();
        $gc->main('host=localhost user=postgres password=postgres dbname=test');
    
        $this->specCurrent = $gc->tables;
        unset($gc);
        return true;
    }
    
    
    
    # ========================================================================
    #
    # Area: Code Translation Stuff
    #
    # ========================================================================
    /**
     * Placeholder for platform-specific overrides
     * 
     * @param $platform 
     * @param $code
     * @return String translated code
     */
    protected function codeTranslate($code) {
    }
    
    
    # ========================================================================
    #
    # Area: Write out debug information
    #
    # ========================================================================
    /**
     * Writes interal arrays out for debugging.
     *
     *
     * Previous Step: {@link writeDictionary}
     *
     * Next Step: {@link logWrapUp}
     *
     * @return Boolean Always Returns True
     */
    private function writeDebug() {
        # Get the directory, make sure there is a debug dir
        $dir = $this->dir;
        $debug=$dir.'debug/';
        $this->log("Debug directory is $debug");
        if(!is_dir($debug)) mkdir($debug);
        
        # Now just write out the debugging results
        $debugText = '';
        $debugTotal = '';
        $debugTotal.=$this->writeDebugArray($debug,$debugText,'lines');      
        $debugTotal.=$this->writeDebugArray($debug,$debugText,'dir');      
        $debugTotal.=$this->writeDebugArray($debug,$debugText,'progdir');      
        $debugTotal.=$this->writeDebugArray($debug,$debugText,'errors');      
        $debugTotal.=$this->writeDebugArray($debug,$debugText,'warnings');      
        $debugTotal.=$this->writeDebugArray($debug,$debugText,'files');      
        $debugTotal.=$this->writeDebugArray($debug,$debugText,'specs');      
        $debugTotal.=$this->writeDebugArray($debug,$debugText,'mergeHistory');      
        $debugTotal.=$this->writeDebugArray($debug,$debugText,'spec');      
        $debugTotal.=$this->writeDebugArray($debug,$debugText,'spec2');      
        $debugTotal.=$this->writeDebugArray($debug,$debugText,'specExtra');      
        $debugTotal.=$this->writeDebugArray($debug,$debugText,'tables2');      
        $debugTotal.=$this->writeDebugArray($debug,$debugText,'lists');      
        $debugTotal.=$this->writeDebugArray($debug,$debugText,'keySequence');
        $this->writeDebugArray($debug,$debugText,'colSequence');      
        $debugTotal.=$this->writeDebugArray($debug,$debugText,'triggers');      
        $debugTotal.=$this->writeDebugArray(
            $debug,$debugText,'platformProblems'
        );      
        $debugTotal.=$this->writeDebugArray(
            $debug,$debugText,'builds'
        );      
        
        file_put_contents($debug."ALL.TXT",$debugText);
        file_put_contents($debug."ALL.HTML",$debugTotal);
        
        # Some hard-coded stuff just for errors
        if ($this->errorCount==0) {
            file_put_contents($debug."errors.count.txt",'');
        }
        else {
            file_put_contents($debug."errors.count.txt",$this->errorCount);
            file_put_contents($debug."errors.json",$this->json_format(json_encode($this->errors)));
            $yaml = Spyc::yamlDump($this->errors,4);
            file_put_contents($debug."errors.yaml",$yaml);
        }
        
    }
    
    function writeDebugArray($debug,&$debugText,$property) {
        # Dump the array and capture it
        # KFD 12/18/10 Modified in case it does not exist
        if(!isset($this->$property)) {
            $text="Property not defined: \$this->$property";
        }
        else {
            ob_start();
            print_r($this->$property);
            $text = ob_get_clean();
        }
            
        $debugText
            .="=============================="
            ."\n$property"
            ."\n=============================="
            ."\n$text"
            ."\n\n";
        
        # And of course write it out
        file_put_contents($debug.$property.'.txt',$text);
        
        # append it to universal list
        ob_start();
        return ob_get_clean();
    }
    
    # ========================================================================
    #
    # Area: Logs, errors, and warnings
    #
    # ========================================================================
    /**
     * Begins a new stage in the log
     * 
     * Outputs a special message stating that a new stage is beginning.
     * 
     * If a previous stage was opened, calls {@link logStageClose}.
     * 
     * Makes use of {@link stage}, {@link timeStage}, and calls {@link log}.
     * 
     * @see log
     * @see logStageClose
     * @see logWrapUp
     * @param $stage
     * @param $description
     * @return unknown_type
     */
    private function logStage($stage,$description) {
        # Close an existing stage if open
        if($this->stage<>'') $this->logStageClose();
        
        # Write the properties for this stage
        $this->timeStage = time();
        $this->stage = $stage;
        
        # Write a log message
        $this->log("--------------------------------------------------------");
        $this->log("BEGIN STAGE: ".$stage.", ".$description);
        $this->log("");
    }
    /**
     * Logs elapsed time for stage and running total.
     * 
     * Reads {@link timeStage} to generate an elapsed time message for
     * the stage that just closed, and reads {@link time} to generate
     * an elapsed time message for the entire process so far.
     * 
     * Clears the variable {@link stage}, to flag that no stage is open.
     * 
     * @see log
     * @see logStage
     * @see logWrapUp
     * 
     * @return unknown_type
     */
    private function logStageClose() {
        $this->log();
        $time1 = $this->logTime(time() - $this->timeStage);
        $time2 = $this->logTime(time() - $this->time);
        $this->log("STAGE ".$this->stage." complete");
        $this->log(" elapsed time this stage: ".$time1);
        $this->log(" total elapsed time     : ".$time2);
        $this->log("");
        
    }
    /**
     * Formats seconds into mm:ss
     * 
     * @param $seconds
     * @return unknown_type
     */
    private function logTime($seconds) {
        $xsec = $seconds % 60;
        $xmin = ($seconds - $xsec)/60;
        $xsec = str_pad($xsec,'2','0',STR_PAD_LEFT);     
        $xmin = str_pad($xmin,'2','0',STR_PAD_LEFT);
        return "$xmin:$xsec";     
    }
    /**
     * Outputs a string of text to the log.
     * 
     * At this time all log messages are echoed to standard output.
     * 
     * @see logStage
     * @see logStageClose
     * @see logWrapUp
     * @param $text
     * @return unknown_type
     */
    private function log($text='') {
        echo "\n$text";
    }
    /**
     * Record a warning for reporting later.
     * 
     * Warnings are stored in the array {@link warnings}, and a
     * running count of warnings is stored in {@link warningCount}.
     * 
     * @param $text
     * @return unknown_type
     */
    private function warning($text) {
        $this->warnings[$this->stage][] = $text;
        $this->warningCount++;
    }
    /**
     * Record an error for reporting later.
     * 
     * Warnings are stored in the array {@link errors}, and a
     * running count of warnings is stored in {@link errorCount}.
     *
     * @param $text
     * @return unknown_type
     */
    private function error($text,$code='') {
        if($code<>'') $code="E_".$code.'  ';
        $this->errors[$this->stage][] = $code.$text;
        $this->errorCount++;
    }
    /**
     * Log all warnings and errors and log final elapsed time.
     * 
     * Previous Step: {@link writeDebug}
     *
     * Next Step: None, this is the end.
     * 
     * @see log
     * @see logStage
     * @see logStageClose
     * @return unknown_type
     */
    private function logWrapUp() {
        /*
        # Report on problems for particular platforms
        $this->log("--------------------------------------------------------");
        $this->log("Platform suitability report");
        $this->log();
        foreach($this->platformProblems as $platform=>$problems) {
            if(count($problems)>0) {
                $this->log();
                $this->log(" -- PLATFORM PROBLEM -- ");
                $this->log(" -- THIS DATABASE CANNOT BE BUILT FOR '$platform':");
                foreach($problems as $problem) {
                    $this->log();
                    $this->log($problem);
                }
            }
        }
        foreach($this->platformProblems as $platform=>$problems) {
            if(count($problems)==0) {
                $this->log();
                $this->log("This database is ok for '$platform'");
            }            
        }
        */
        
        
        $this->log("--------------------------------------------------------");
        $this->log("Errors and Warnings Report");
        $this->log();
        if($this->errorCount) {
            $this->log("There are ".$this->errorCount." errors:");
            foreach($this->errors as $section=>$messages) {
                foreach($messages as $message) {
                    $this->log();
                    $this->log("$section: $message");
                }
            }
            $this->log("");
            $this->log("");
        }
        if($this->warningCount) {
            $this->log("There are ".$this->warningCount." warnings:");
            foreach($this->warnings as $section=>$messages) {
                foreach($messages as $message) {
                    $this->log();
                    $this->log("$section: $message");
                }
            }
            $this->log("");
            $this->log("");
        }
        
        
        $this->log();
        $this->log("Count of errors   : ".$this->errorCount);
        $this->log("Count of warnings : ".$this->warningCount);
        $time = $this->logTime(time() - $this->time);
        $this->log("Final elapsed time: ".$time);
        $this->log();
        $this->log();
    }
    # ========================================================================
    #
    # Area: Super short utilities
    #
    # ========================================================================
    /**
     * Return true if there are no errors.
     * @return boolean True if no errors
     */
    private function ok() {
        return $this->errorCount > 0 ? false : true;
    } 
    
    /**
     * Safe array access.
     * 
     * This routine allows you to access array properties that may not
     * exist without complicated isset() calls.  Just call this routine
     * with the array, the name of the key, and a value to provide if
     * the key does not exist.  Makes for smoother code.
     * 
     * @param $array The array to examine
     * @param $key The key we are looking for
     * @param $default The return value if key is not found
     * @return mixed
     */
    protected function arr($array,$key,$default='') {
        if(isset($array[$key])) {
            return $array[$key];
        }
        else {
            return $default;
        }
    }
    
    /**
     * Safe array access and remove key
     * 
     * This routine is exactly like {@link arr}, except if the
     * key exists it is removed.
     * 
     * NOTE: Written 5/24/09, but not actually used.  Seems useful
     * so I did not remove it.  If it is not being used when we
     * are ready to release, it should be removed.
     * 
     * @param $array
     * @param $key
     * @param $default
     * @return mixed
     */
    private function pop(&$array,$key,$default='') {
        if(isset($array[$key])) {
            $retval = $array[$key];
            unset($array[$key]);
            return $retval;
        }
        else {
            return $default;
        }
    }
    
    /**
     * Accepts code snippet and indents every line x spaces.
     * 
     * @param String The input snippet as a string with newlines
     * @param Number The number of spaces to indent each line
     * @return String The indented lines
     */
    protected function indent($input,$spaces=4) {
        $pad = str_repeat(' ',$spaces);
        $x = explode("\n",$input);
        return $pad.rtrim(implode("\n$pad",$x))."\n";
    }
    
    /**
     * Returns a columns properties, or false if does not exist
     * 
     * @param $table Table name
     * @param $column Column name
     * @return Array The column's properties 
     */
    private function column($table,$column) {
        if(!isset($this->tables2[$table]['columns'][$column])) {
            return false;
        }
        else {
            return $this->tables2[$table]['columns'][$column]['properties'];
        }
    }
    
    /**
     * Returns a property of a column in a table.
     * 
     * This function returns a specific property of a table column.
     * It is a shortcut to avoid deeply nested array expressions.
     * 
     * This program does not check to see if the property exists,
     * if you pull a non-existent property the results are 
     * undefined.
     * 
     * @param $table Table Name
     * @param $column Column Name
     * @param $property Property
     * @return String the property value
     */
    protected function columnProp($table,$column,$property) {
        # The conditional is required because this may be called
        # on invalid properties due to errors in the .dd.yaml,
        # and we do not want to throw notices
        if (isset($this->tables2[$table]['columns'][$column]['properties'][$property])) {
            return $this->tables2[$table]['columns'][$column]['properties'][$property];
        }
        else {
            return '';
        }
    }

    protected function selfJoin($table,$new='@new_',$old='@old_') {
        $pks  = explode(
        	',',$this->tables2[$table]['properties']['primary_key']
        );
        $pktab = $pkins = array();
        foreach($pks as $pk) {
            $pktab[] = "$new$pk = $old$pk";
        }
        $pktab  = implode(' AND ',$pktab);

        return $pktab;
    }

    protected function fkSelfJoin($fkdetails,$new='@new_',$old='@old_') {
        #$expr = $not ? '<>' : '=';
        $ax = array();
        foreach($fkdetails['properties']['colschd'] as $col) {
            $ax[] = "$new$col = $old$col";
        }
        return implode(" AND ",$ax);
    }
    
    protected function joinParent($fkdetails,$chd='',$par='') {
        $where  = $fkdetails['properties']['sql'];
        $list1 = explode(" AND ",$where);
        $x = array_shift($list1);
        $list2 = explode(" = ",$x);
        list($tabchd,$throwaway)=explode(".",$list2[0]);
        list($tabpar,$throwaway)=explode(".",$list2[1]);
        if($chd) {
            $where = str_replace("$tabchd.",$chd,$where);
        }
        if($par) {
            $where = str_replace("$tabpar.",$par,$where);
        }
        return $where;
    }
    
    protected function genError($platform,$string) {
        $matches=array();
        preg_match("/\{.*\}/",$string,$matches);
        $segments = explode(":",$matches[0]);
        $code = $segments[1];
        $english = $this->errInfo[$code]['english'];
        
        switch($platform) {
            case 'mssql':
                $rv="RAISERROR('$string $english',17,1);\n        BREAK;\n";
                break;
            case 'mysql':
                $matches=array();
                preg_match("/\{.*\}/",$string,$matches);
                $match = substr($matches[0],1);
                $match = rtrim($match,"}");
                $rv    = "CALL ".str_replace(":","__",$match)."();\n";
                break;
            case 'postgres':
                $rv="RAISE EXCEPTION '$string $english';\n";
                break;           
        }
        return $rv;
    }
    
    /**
     * Accepts type and value returns string for direct SQL commands
     *
     * @param $xtype
     * @param $value
     * @return String a safe sql-formatted value
     */
    private function sqlFormat($xtype,$value) {
        switch($xtype) {
            case 'tinyint':
            case 'smallint':
            case 'mediumint':
            case 'int':
            case 'bigint':
            case 'float':
            case 'double':
            case 'money':
            case 'decimal':
            case 'numeric':
            case 'year':
                return str_replace("'","''",$value);
                break;
            case 'date':
                return "'".date('Y-m-d',strtotime($value))."'";
                break;
            case 'datetime':
                return "'".date('Y-m-d G:i:s',strtotime($value))."'";
                break;
            case 'char':
            case 'varchar':
            case 'clob':
            case 'text':
            case 'tinytext':
            case 'mediumtext':
            case 'longtext':
            case 'varbinary':
            case 'tinyblob':
            case 'longblob':
                return "'".str_replace("'","''",$value)."'";
                break;
            default:
                return "'".str_replace("'","''",$value)."'";
        }
    }
    
    
    
    /**
     * Shortcut to retrieve values from {@link specExtra}
     * 
     * The array {@link specExtra} contains additional snippets that
     * can be used in various places.  Accessing the array is a bit
     * of a pain because you are looking 3 levels deep for an element
     * that might not exist.  
     * 
     * This function gives you a shortcut by testing if the property
     * exists, returning it if so, and returning an empty string
     * otherwise.
     * 
     * @param $platform The platform
     * @param $fullname The element
     * @param $property The property
     * @return String the property value or empty string
     */
    private function arrExtra($platform,$fullname,$property) {
       if(isset($this->specExtra[$platform][$fullname][$property])) {
           return $this->specExtra[$platform][$fullname][$property];
       } 
       return '';
    }
    
    /**
     * Creates a text description of an object's definition history
     * 
     * This program accepts either an array or a "fullname" property.
     * If it receives an array, it looks for $array['properties']['fullname'].
     * 
     * This program then looks at {@link mergeHistory} and generates a
     * text description of all locations the object was defined in. 
     * 
     * @param $mixed 
     * @return $string A textual description of all definitions
     */
    private function defineHistory($mixed) {
        $name = is_array($mixed) ? $mixed['properties']['fullname'] : $mixed;
        
        # Begin with the item itself
        $mh = &$this->mergeHistory[$name];
        $retarray = array('File '.$mh['file'].' line '.$mh['line']);
        
        $extends = $this->arr($mh,'extends',array());
        #if(count($extends)>0) {
            # get rid of last entry, the 'literals' entry 
        #    array_pop($extends);
        #}

        array_reverse($extends);
        foreach($extends as $source=>$x) {
            if($source=='--literals--') continue;
            $retarray[] = 'File '.$x['file'].' line '.$x['line'];
        }

        return "Defined in: ".implode(", ",$retarray).'.';
    }
    
    /**
     * Formats JSON string as human readable
     *  
     * 
     * @param $string JSON string
     * @return $string Human readable JSON
     */
    private function json_format($json) { 
        $tab = "  "; 
        $new_json = ""; 
        $indent_level = 0; 
        $in_string = false; 

        $json_obj = json_decode($json); 

        if($json_obj === false) 
            return false; 

        $json = json_encode($json_obj); 
        $len = strlen($json); 

        for($c = 0; $c < $len; $c++) 
        { 
            $char = $json[$c]; 
            switch($char) 
            { 
                case '{': 
                case '[': 
                    if(!$in_string) 
                    { 
                        $new_json .= $char . "\n" . str_repeat($tab, $indent_level+1); 
                        $indent_level++; 
                    } 
                    else 
                    { 
                        $new_json .= $char; 
                    } 
                    break; 
                case '}': 
                case ']': 
                    if(!$in_string) 
                    { 
                        $indent_level--; 
                        $new_json .= "\n" . str_repeat($tab, $indent_level) . $char; 
                    } 
                    else 
                    { 
                        $new_json .= $char; 
                    } 
                    break; 
                case ',': 
                    if(!$in_string) 
                    { 
                        $new_json .= ",\n" . str_repeat($tab, $indent_level); 
                    } 
                    else 
                    { 
                        $new_json .= $char; 
                    } 
                    break; 
                case ':': 
                    if(!$in_string) 
                    { 
                        $new_json .= ": "; 
                    } 
                    else 
                    { 
                        $new_json .= $char; 
                    } 
                    break; 
                case '"': 
                    if($c > 0 && $json[$c-1] != '\\') 
                    { 
                        $in_string = !$in_string; 
                    } 
                default: 
                    $new_json .= $char; 
                    break;                    
            } 
        } 

        return $new_json; 
    } 
}
?>
